Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Links to queries
- Thread starter jwjw
- Start date
johnnymac43
MIS
However, the Documenter (Tools, Analyse, Documenter in Access 2000) may be of some help, as it will produce a report containing the SQL for each object selected.
For queries, deselect all options except SQL (select 'Nothing' for Fields and Indexes).
For reports and forms, deselect all options except Code.
HTH
John
For queries, deselect all options except SQL (select 'Nothing' for Fields and Indexes).
For reports and forms, deselect all options except Code.
HTH
John
annsolomon
MIS
Several months ago we purchased Access Analyzer software from FMS. (I think it was $295.) It's a wonderful tool and will go a long way in helping with your clean up task. That isn't to say you won't still feel the pain. No way around it completely. This software has lots of super reports. If you want to take a look their website is
Note: I have no affiliation with that company whatsoever. It's just that I recently went through your scenario and found this software to be a big help. Ann
Note: I have no affiliation with that company whatsoever. It's just that I recently went through your scenario and found this software to be a big help. Ann
MichaelRed
Programmer
another 'tool' in the same venue is "speed ferrit". I do not own or use it, but it does have a lot of handy tools.
A different approach, which I have used, may be apopropiate.
Create a NEW database. Copy the objects which the User community MOST often uses (forms and reports). Examine the Recordsource of these objects and copyu them into the NEW db. Start to "use" the new db (it will 'crash' frequently). Errors will generally refer to the specific object which is MISSING. Copy the missing object (Table/Query/Function/...) to the new db. After much ado about errors, the frequency of the errors will taper off. Get some REAL users to continue the testing. Which a few users are satisified, "declare" the process complete and switch all users to the new db. Expect another round of errors and complaints - indicating still MORE objects which need to be copied from the old ver. At SOME point, the errors / complaints will be reduced to the more mundane issues of just what they want changed. then, yoiu really are done with that phase.
An alternative (but nearly as messy) approach is to attempt to guess which objects are more or less obsolete. ReName these (I use "XXX_" as a prefix to the existing name). When/If a user has an error / complains and the error refers to an object which exists (with the prefix), restore the original name. After some period of time (1 Month?), MOVE the renammed objects out of the production db into a "rarely if ever needed backup" db.
Another but entirely seperate exercise would be to actually analyse the needs or the user community and design a completly new db app which meets those needs. Of course this would generally be along the same lines as the existing app, but since you start from scratch, it will only include the specific items needed to suypport the 'design requirement' (garnered from user interviews). I know it is a radical approach, but it could actually not take any longer than the bumbling through what is referenced where and it is necessary AND useful ...
Seperatly, AGAIN, it is quite easy to 'roll your own' -if somewhat limited cross reference listing, and use this as at least a starting point for the pruning.
The Documents collection in Ms. Access includes all db object types. Each collection contains all of the objects of the parent type. A relatively simple loop process would be able to "search" each collection and retrieve the relevant properties (RecordSource for Forms and Reports, ControlSource for Controls, etc). An "array" (I used a table) could be constructed to include each "Object", (Form, report, ...) and the source(s) referenced in hte object. From this perspective, a seperate listing of UNREFERENCED items / objects would be the (imperfect) list of canidates for removal (via one of the previously discussed approaches).
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
A different approach, which I have used, may be apopropiate.
Create a NEW database. Copy the objects which the User community MOST often uses (forms and reports). Examine the Recordsource of these objects and copyu them into the NEW db. Start to "use" the new db (it will 'crash' frequently). Errors will generally refer to the specific object which is MISSING. Copy the missing object (Table/Query/Function/...) to the new db. After much ado about errors, the frequency of the errors will taper off. Get some REAL users to continue the testing. Which a few users are satisified, "declare" the process complete and switch all users to the new db. Expect another round of errors and complaints - indicating still MORE objects which need to be copied from the old ver. At SOME point, the errors / complaints will be reduced to the more mundane issues of just what they want changed. then, yoiu really are done with that phase.
An alternative (but nearly as messy) approach is to attempt to guess which objects are more or less obsolete. ReName these (I use "XXX_" as a prefix to the existing name). When/If a user has an error / complains and the error refers to an object which exists (with the prefix), restore the original name. After some period of time (1 Month?), MOVE the renammed objects out of the production db into a "rarely if ever needed backup" db.
Another but entirely seperate exercise would be to actually analyse the needs or the user community and design a completly new db app which meets those needs. Of course this would generally be along the same lines as the existing app, but since you start from scratch, it will only include the specific items needed to suypport the 'design requirement' (garnered from user interviews). I know it is a radical approach, but it could actually not take any longer than the bumbling through what is referenced where and it is necessary AND useful ...
Seperatly, AGAIN, it is quite easy to 'roll your own' -if somewhat limited cross reference listing, and use this as at least a starting point for the pruning.
The Documents collection in Ms. Access includes all db object types. Each collection contains all of the objects of the parent type. A relatively simple loop process would be able to "search" each collection and retrieve the relevant properties (RecordSource for Forms and Reports, ControlSource for Controls, etc). An "array" (I used a table) could be constructed to include each "Object", (Form, report, ...) and the source(s) referenced in hte object. From this perspective, a seperate listing of UNREFERENCED items / objects would be the (imperfect) list of canidates for removal (via one of the previously discussed approaches).
MichaelRed
m.red@att.net
Searching for employment in all the wrong places
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
