I have been given the task of updating our access 97 database.
When it was created they just kept rewriting the queries, forms, etc...
Now before I attempt to update to a more recent version I would like to remove all of the unused junk, But I do not know where to begin. Any help would be a great help.

Thanks Randall

Here's a possible framework:

1. If there is an Autoexec macro, trace each step of its execution and write down any object (form, report, macro, module) it uses.

2. The next step consists in identifying the forms and reports that are actually used (outside a possible Autoexec macro).

3. For each form or report collected in 1 and 2, you then must determine which tables/queries they use (RecordSource property).

4. For each form or report collected in 1. and 2., examine their class module to detect any reference to functions in other modules, macros, use of tables or queries (either by opening a RecordSet, using a domain function or building dynamic SQL querie).

5. Create a new database and import the objects ih the list assembled in the steps 1 to 4.

6. Write down the references used in the original database and add any missing reference to the new database accordingly.

7. Try to compile it. if you receive errors (Unknown Function), you'll know that one or several modules are missing. Identify them then import them in the new database and try compiling again.

8. When you are able to successfully compile the new database, test each functionalities, note any error and identify the missing object. Import it and try again.

Sinndho,
Thanks that is something to work with. With the size of this it will take awhile, but you have to start somewhere.

You're welcome!

personally if it aint broke dont touch it

by all means spend time understanding the tables, queries, forms and reports, but providing its meeting the user needs leave well alone. unless there is a good reason to make changes

say the performance is iffy (usual suspect would be over use of domain functions such as dum, dmax, average and so on, especially in reports.

say there's more stuff needing adding

or if you have been explicitly told there's problems.

before making changes spend some time with the users to make certain that changes being made are actually important to the business and not just 'da boss'

if there's extensive use of VBA code behind the application then get to grips with the authors style and way of doing things. just because author A does things one way doesn't make it wrong, unless the code uses a style or approach that is deemed deprecated or uses libraries that are no longer supported

the problem with removing junk code is that unless you truly truly get to grips with the system then its all to easy to delete something thats used infrequently (such as year end processing) and then have to reinstate stuff pronto. at year end I prefer sleeping in my bed, as opposed to tearing my hair out trying to work out why someone or something tinkered with this *&%^(*(*& code

I have to agree with the Jaded Developer, here! But if you're determined to persue it, here's a couple of points I'd do:

1. Copy the current Access File and keep the copy safe!
2. Send out a list of all Queries and Reports found, asking all users in the company to identify those they currently utilize
3. Ask users to identify any of the above that they currently have problems with

Concerning #1 above, I'd actually make several copies, including one to a CD disc.

Concerning #2 above, you need your users to include any seasonal Queries/Reports that they use seasonally, although not necessarily at the moment, such as the aforementioned end-of-year Reports.

Concerning #3 above, impress on the users that you're only concerned with problems they are currently experiencing with things acting up, not on new things they wish they could do! Time for those things will come after you get the current app trimmed down and functioning correctly again. Trying to do all of this at once is a sure fire recipe for disaster!

Linq ;0)>