Table Name Changed...need to change many queries 3

[SarasotaIT]

I have a database with 30 queries based on one table: tblCustomers. The table name was changed to tblCustomersOld. How can I change all 30 queries to reflect the new table name without having to open each query and edit individually? My expertise is SQL; I am not a VBA programmer but can stumble through it if I need to.

Thanks in advance!!
Terry

 

[PHV]

Why not create a saved query named tblCustomers ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssecca]

If you have repaleced the old tblCustomers with a new table "tblCustomers" as well as the new table "tblCustomersOld" then PHV's solution will not work as you can not have both a table and a query named the same. At one time I use to use a program called "Speed Ferret" (or something like that) to do find and replaces through out the application. I have not upgraded since moving to Access(XP) so don't know if it is available or not. Short of that the esyest way, I think, is to do it in a text editor. go into the query design view and view the query in a SQL window. cut and paste the SQL to a text editor do a find and replace then past the fixed SQL back into the design window and save.

Good Luck

 

[TheAceMan1]

How are ya SarasotaIT . . . . .

The following code requires the [purple]Microsoft DAO 3.6 Object Library[/purple]. In any code/VBE window, click [blue]Tools[/blue] - [blue]References[/blue]. Find the library in the list and [blue]add a checkmark[/blue]. Then with the [purple]up arrow[/purple], push it up as high in priority as it will go. Click [blue]OK[/blue].

[purple]Note: backup the database before you run the code . . . just to be safe! . . .[/purple]

In a module in the modules window, copy/paste the following code:

[blue]Public Function qryReplace(strSearch As String, strReplace As String)
   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   
   Set db = CurrentDb()
   
   DoCmd.Hourglass True
      For Each qdf In db.QueryDefs
         qdf.SQL = Replace(qdf.SQL, strSearch, strReplace)
      Next
   DoCmd.Hourglass False
   
   Set db = Nothing
   
End Function[/blue]

[purple]The code will loop thru all queries replacing all instances of strSearch with strReplace.[/purple]

To call the function:

[blue]   Call qryReplace("[purple][b]tblCustomers[/b][/purple]", "[purple][b]tblCustomersOld[/b][/purple]")[/blue]

Note: Make sure no queries are in use when you run the function . . .

Thats it . . . give it a whirl & let me know . . . .

See Ya! . . . . . .

 

[SarasotaIT]

I am doing very well - now - thanks to your help!!! Your code worked like a charm! AWESOME, detailed response! I am going to file it away for future reference!

Thanks again, AceMan1!
Terry

 

[willir]

Cool stuff AceMan

 

[PHV]

Unfortunatly the excellent TheAceMan1's function don't deal with any occurence of the table used in code, like RowSource, 2nd argument of domain functions, dynamically build sql, ...