Existing query: How to change the source table?

[floridaboy2004]

I have a quick question for the experts...

If I am working with a pre-existing database and I would like to use a pre-existing query, is there a way to change the source table used to create it in the first place? I am somewhat unfamilar with this database and would like to avoid recreating things. Also, if there is a simple way of doing this with forms and reports as well, please let me know. Thanks again, all! <:P

 

[Norie]

ASFAIK there is no easy/quick way to do this without using VBA code.

Do the existing table and the new table you want to use have the same structure?

 

[Joe4]

If you go into the Query Builder mode of a query, you can see what tables/queries were used in setting it up and you can Add/Delete more as you see fit.

If you are looking at replacing the source, but not the fields returned, I would recommend adding the new source first, replacing the fields to display to pull from this new source, then delete the old source.

On Forms/Reports, if you click on Properties for that Form/Report, you can easily change the Control Source to pull from a different source.

 

[floridaboy2004]

jmiskey,

Whenever I open a specific form, it gives me the following message:

"Microsoft Access can't find the form 'Customer_list' referred to in a macro expression or Visual Basic code.

*The form you referenced may be closed or may not exist in the database.
*Microsoft Access may have encountered a compile error in a Visual Basic module for the form."

What does this mean? I have removed/changed the name of some forms, tables, etc. since working with this database.

Can you give me specifics on how to change the source of a query? Do you mean use DESIGN mode?

Also, can you give me specifics on the forms/reports?

Thanks for the replies, everyone! [/img]

 

[Joe4]

Yes, I mean open all queries/forms/reports in Design mode.

What does this mean? I have removed/changed the name of some forms, tables, etc. since working with this database

It is generally a very bad idea to do this with a database that you are familiar with. The reason why you are getting most of these errors is that when you change object names (names of table, queries, forms, and reports), Access is smart enough to change them in the other related object that use them.

HOWEVER, if there is VBA code/macros used in the application (which sounds like there is), names of objects are NOT automatically changed in your macros/VBA code. So by changing the names of objects, you created many errors in the VBA code & macros that reference them.

If you can create a mapping from the old name of each object to the new name you gave it, you can go into the VBA code (ALT-F11) and do Search and Replaces on the whole Project to correct all the object references (or go through the code manually line-by-line). And go through all the macros too, changing the object references where needed.

 

[SydneyGeek]

Adding to Norie and jmiskey's comments --

If the structure of the original and new tables are IDENTICAL, you can do this:
1. Open the query in Design view, and switch to SQL view.
2. Copy the SQL to Notepad and use the Replace command to change the table names. Then copy the new SQL back, replacing the original.
3. Take a look in Datasheet view. If all is OK, close and save. If you get parameter popups, go back to Design view and fix the field names that didn't work. If you can't get it going, close the query without saving changes and you haven't lost anything.

Denis