Wicklunda
asked on
Cleanup Access Database of old Tables and Queries?
I have a project where I have some huga Access databases (250+ mb after compact) that have hundreds of unused tables and other objects. The problem is, which ones?
My question is: how would you proceed with this type of project? i.e. would you use a third party tool, what methodology, etc.
My question is: how would you proceed with this type of project? i.e. would you use a third party tool, what methodology, etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know I could go through pretty easily and identify the day to day stuff, but I'm afraid of abandoning items used at Qtr or year-end only.
One thought I had was to create the new DB with links to the old. That way if items were missing the old DB would still be current. After a period the when the user-group felt comfortable we could port over.
Another possibility might be to use a replication strategy either way.
The third is to bite the bullet and move over and plan to support the "gotcha's!" as service tickets. (Not my preferred choice)
Anyone approched these in these ways or others to suggest?
One thought I had was to create the new DB with links to the old. That way if items were missing the old DB would still be current. After a period the when the user-group felt comfortable we could port over.
Another possibility might be to use a replication strategy either way.
The third is to bite the bullet and move over and plan to support the "gotcha's!" as service tickets. (Not my preferred choice)
Anyone approched these in these ways or others to suggest?
Much like OMgang suggested ... find out first what you *do* need. This typically can be most easily identified by Forms and Reports, follwed by the queries, tables and code needed to support them. I alos remove as many code library refeences as possuible and only add them back in as necessary. Not easy, not quick but it will get the job done. The trick is to have a developent methodology for the furure that makes it easier to find the _OLD stuff. Too many times seen Query1, Query1_NEW, query2 ... that are really only different versions of one report's recordsource. What I do when I create a new version it to do exactly that, create a new version in a seperate folder of the whole file and when I need to modify something, I usually copy the object, give it the same name and suffic _OLD (quick roll back if I screw it up). Before I start my system testing I delete everything with _OLD at the end, this helps to keep things clean. I really dislike the built-in dependencies functionality and typically hand type the dependencies into each object's properties ... so I always know where every single query is used. This also helps when I want to change a query for one combobox I can easily see that it will change a different combobox on another form.
Steve
Steve
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thnak you for all your help!
fms has great tools ... but they can't tell you if there is a form that gets opened by a button_click that no one ever uses.
Also note that Access 97 can go to 1GB, and Acc 2K, XP, and 2k3 can go 2GB. 250 MB is not radical.