Finding Non-Used Table Fields

Hi,

I've inherited a large database that has gone through many revisions. Is there a way to find fields that are not being used by any of the forms that work on the tables? I'm thinking Data Analyser but don't think it will tell what isn't being used. This would be like a program that shows unused variables but in this case it would find unused table fields.

Any Access people out there that know the Database Documenter well enough to give this a try?

Marvin, I have not used the documenter. This may be onerous, but you could run an aggregate query on each table that did a count of the records for each field. If you came up with a zero count, then you would at least know that there were no records with data in that field. Just trying to think outside the box on this one.

Alan

Hey Alan,

Thanks for the thoughts. If Excel is big with lots of funcitons and features then Access seems to be at least 4 times bigger. Is there anyone that knows it all? Someone else suggested removing the fields from the table and then look at all tables and run all queries and reports and if they didn't break then the field (that was removed) had no use. I think that would be a method to find Non-Used Fields. I think your answer is more of what field has no data in it, as I envision the aggregate query.

Got a good suggestion for books or examples that will shorten my learning curve time (using Access 2010)?

Hey Marvin. I am in the same boat you're in. I found this link last week http://www.devhut.net/2011/04/08/ms-...field-is-used/
I figure (if Daniel's code works) I can loop through the fields in a table...and possibly through each table to compile a list of used or unused fields and the tables they live in. If you come up with a method before I do, I'd be pleased as punch if you'd post it here. It may be the weekend before I have a chance to work on this further. Thanks!

I used the "The Complete Reference Access 2002" when I started out. By Virginia Anderson. I am sure that there is an updated version.

Web sites I frequent in no particular order

http://allenbrowne.com/tips.html
http://www.datapigtechnologies.com/AccessMain.htm
http://www.datawright.com.au/
http://www.btabdevelopment.com/main/...6/Default.aspx
http://www.databasedev.co.uk/general.html
http://hitechcoach.com/
http://www.access-diva.com/

There are many more sites.

While I was perusing my list of sites, I found something that may be helpful to both of you. Look at Crystal's Analyzer here

http://www.accessmvp.com/Strive4Peace/Analyzer.htm

Alan

Hey Alan,

Thanks for all the leads. I've got to look through them a little better. I tried the Crystal Analyzer and it gives a lot of info but not what is missing. I finally ran my Database Documenter on Forms and Reports only. I only looked at the Property of DATA and exported it to Word. Then using the Word Doc, I searched for the Field Name. If I didn't find it, then that field is not being used for any form or report. The bad thing is that the same name Field is in a second table and being used only a few times. I needed to look hard at the word doc to make sure it wasn't being used by any Form or Report, needing to see the Table info also.

Access seems to be harder than Excel. I'm guessing there are about 5 times more things to deal with. Do you have a guess at the complexity or number of features in Access compared to Excel? Queries and SQL vies are ok but using the Builder and Subforms and RowSource properties have me always scratching my head. I need to just sit down and learn where to find stuff and how it drives from one place to another. Also 2010 have Macros that are new to me and I wish it had a Macro Recorder like Excel, so I could translate what it thinks it is doing so I could look at it.

All versions of Access have macros - they are what was there before VBA (a little like XLM in Excel). A macro recorder in Access would be largely pointless, I think, since it is a completely different beast to Excel.
If you really want to know what fields are being used, you would need to look through code as well, really.

Marvin, I don't use Macros at all in Access. I stick with VBA. You should know that if you use a wizard to create anything, that in 2007 and 2010, the automation is recorded as a macro and not as VBA as in prior versions. However, having said that, all Macros can be converted to VBA. Check on the Ribbon for it. I think its on the developer tab. I know that you have inherited a db, but you may want to try an build one on your own to get the feel of how it really works. Perhaps something simple. Document your CD collection, build a contact list or track your gas fillups and service for your car. Just some suggestions if you don't have a real life issue.

Alan

I initially read that last bit as "Just some suggestions if you don't have a real life".

I could see how that could happen. I sometimes wonder if some people on these forums, based upon the requests we see, do have a real life.