Replace table in Access query

Gerhardpet used Ask the Experts™
I have a complicated query in Access and I was to replace all tables in it with the same table structure but the name are different.

How can I do this without editing the query and do it in design view?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007

Find & Replace - best ever tool.  Get it!


When I need to do this I go to SQL view, copy the query, paste it into a text editor (or Notepad!) and replace the old name with the new one. Then I copy the text back into the SQL view in Access. Simple.

Alternatively, add the new table into design view, and for each column change the "Table:" row to the new table. When that's complete remove the old table from the designer.

I hope that helps.
Hamed NasrRetired IT Professional

One way:
Query: change_table_name_q
Select b.f1 From b;

A lookup table: change_table_name (table_name, changeTo)
table_name      changeTo
b                         a

Click event code:
Private Sub cmdChangeTableName_Click()
    Dim rs As Recordset
    Dim qsql As String
    qsql = CurrentDb.QueryDefs("change_table_name_q").sql
    Set rs = CurrentDb.OpenRecordset("change_table_name")
    Do While Not rs.EOF
        qsql = Replace(qsql, vbCrLf, " ", 1, True) ' replace cr and lf
        qsql = Replace(qsql, ";", " ", 1, True) 'replace ; to space
        qsql = Replace(qsql, " " & rs(0) & ".", " " & rs(1) & ".", 1, True) 'replace table.
        qsql = Replace(qsql, " " & rs(0) & " ", " " & rs(1) & " ", 1, True) 'replace table
    CurrentDb.QueryDefs("change_table_name_q").sql = qsql
End Sub


Find & Replace is a great tool indeed!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial