Replace table in Access query

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?
LVL 1
GerhardpetAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Easy:

Find & Replace - best ever tool.  Get it!

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

mx
0
 
plummetCommented:
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.
0
 
hnasrCommented:
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
        rs.MoveNext
    Loop
    CurrentDb.QueryDefs("change_table_name_q").sql = qsql
End Sub
changeTableName.mdb
0
 
GerhardpetAuthor Commented:
Find & Replace is a great tool indeed!
0
All Courses

From novice to tech pro — start learning today.