We help IT Professionals succeed at work.

Changing Source Table Names in existing complex Query

ARampton
ARampton asked
on
What is the easiest way of changing the name of a source Table (or Query) in a complex Select Query?

I know I can add another Source but I then have to change its Table name for each field column showing before deleting the old Source

I also know I can do a replace in the SQL view but both seem prone to my errors
Comment
Watch Question

BRONZE EXPERT
Top Expert 2016
Commented:
try using this third party tool

http://www.rickworld.com/download.html
Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I tend to do something like the folllowing.  

1.  In the Immediate (debug) window, type:

?currentdb.querydefs(queryname).SQL

Note: replace "queryname" with the name of your query

2.  Copy the SQL string and past it in WordPad, use the Replace functionality to replace the name of the table with your new table name.  Then copy the text and paste it back into the immediate window.

3.  Then, still in the immediate window, do something like the following, where you wrap the SQL string you just pasted into the immediate window within quotes.

currentdb.querydefs(queryname).SQL = "SELECT ....."



Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
You could do this in code as well.  Something like:

Public Sub ReplaceTable(QueryName as string, OldTableName as string, NewTableName as string)

    Dim qdf as dao.querydef
 
    set qdf = currentdb.querydefs(queryname)

    qdf.SQL = replace(qdf.SQL, Oldtablename, newtablename)

    set qdf = nothing

End If

But you have to be careful that the oldtablename is not a subset of some other string.

BRONZE EXPERT

Commented:
go to the query editor menu and hit VIEW -> SQL, then copy and past the text in the notepad, then EDIT -> REPLACE on notepad and copy and past it back to msaccess.
Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I agree with capricorn1 that if you want to do this multiple times, then Rick Fishers Find and Replace is the best.
ARamptonIT Manager

Author

Commented:
Thanks

I will try the Access Add-On tool

It seem Microsoft has omitted any ability to change the Source Table name in the limited properties of a query so I can't edit it like I can almost everywhere else ion Access
BRONZE EXPERT

Commented:
When I need to change the source table or query, I usually make use of aliases. Right click the table and type an alias, e.g. "A", for the source. Then switch to SQL view and notice that all the fields are now prefixed with that alias instead of the original table name. Locate the FROM clause and notice further that the original table name only occurs once in the entire SQL statement. Change the table name there.

    ... FROM ... MyOldTableName AS A ...
    ... FROM ... MyNewTableName As A ...

You can now switch back to the assisted design view.

Cheers
(°v°)
ARamptonIT Manager

Author

Commented:
I have not had time to test utility yet

Explore More ContentExplore courses, solutions, and other research materials related to this topic.