asked on

Changing Source Table Names in existing complex Query

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dale Fye

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 Fye

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.

fabriciofonseca

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 Fye

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

ARampton

ASKER

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

Markus Fischer

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°)

ARampton

ASKER

I have not had time to test utility yet