Finding out what queries are linked to

[Anne Saunders]

How can I find out what form, reports etc I have linked a particular query to? I created a database way way back when I was just learning and now I want to make some changes, delete some old queries, but I am afraid I might delete a query that is linked to something important.

any help will be greatly appreciated

 

[Jon K]

Open each form in design view. In the Properties Sheet, check the Record Source of the Form, and check also the Row Source of any List Box and Combo Box on the form. Note the name of any queries used.

Open each report in design view and check its Record Source.

Now you should have a list of the queries used. For those queries that do not appear in your list, put an X in front of their names.

Run each form and report. If an error message pops up, you'll know which query you have X'ed is still needed. Just remove the X. Eventually you know which queries you can delete.

If you want to be on the safe side, create a new database and export (right click and select Save As/Export) those queries with an X to this new database before deleting them. You can import them back at any time.

 

[Fornatian]

The simplest and best way is not to go all the way and delete the queries entirely, rename them suchas:

Was: qryFormRecordSourceUnknown
To: OLDqryFormRecordSourceUnknown

Then go through every possible use of the db, or leave it for a couple of months and let the users find out for you which queries shouldn't be deleted - Access will surely let you know when it can't find a query, then you can immediately reinstate to their former names and the query calls will work perfectly.

Ian

 

[Anne Saunders]

Thanx. I will try this on Monday at work and let you know.

 

[RichMorrison]

Buy "Speed Ferret". It is a product that searches Access databases for specific references.

Not expensive and well worth it.

RichM

 

[Opengrave]

The simplest and best way is not to go all the way and delete the queries entirely, rename them suchas:

Was: qryFormRecordSourceUnknown
To: OLDqryFormRecordSourceUnknown

Then go through every possible use of the db, or leave it for a couple of months and let the users find out for you which queries shouldn't be deleted - Access will surely let you know when it can't find a query, then you can immediately reinstate to their former names and the query calls will work perfectly.

Ian

A2K note: If you have the 'Name Auto Correct' feature of A2K turned ON this will not work because access will just change all of the refereces to the new name. Make sure you turn this option OFF before doing the above.
TOOLS->OPTIONS->GENERAL->NAME AUTO CORRECT

You should also be able to use the Documenter feature of Access to produce a report of that will tell you all of the properties of your forms and reports. Export this to Word then do a search for your query names.

 

[Fornatian]

1. I am a luddite and know nothing of such technical advances suchas the tricorder and Access 2000.

2. I am not sure if what you say is correct because AutoName correct may change the name of the recordsource named query but what if that query is based on another query and so on - does it maintain integrity through out even when the query is a not named as a specific property - seems dubious but as I say I am an ignorant peasant.

Ian

 

[Opengrave]

Fornatian

1) Besides client server stuff I work on mainframes. The client is slow to upgrade things that seem to work so we have finally gotten them to 'upgrade' to 1985 technology from mid 1970's technology. Anything that is not monochrome scares these guys. I know exactly how you feel.

2) A quick test confirms you are correct. I created a QUERY1 then created QUERY2 based on QUERY1 then changed the name of QUERY1 to QUERYX and QUERY2 failed to work properly. Seems this feature only really works for properties of forms, reports, etc... It obviously would also not work for SQL inside VB code.