Replacing Tables in Queries

[NBVC]

How can I replace the tables referenced in my queries with another table?

I am appending and updating to tables in our ERP database... I did a test run on a SANDBOX version of the database to make sure everything went smoothly.

Now I want to run those queries on our Live environment... can I easily replace the table references in the queries to new tables that update our Live Database once run?

 

[NBVC]

I think I have managed it.. but please advise if I am doing it wrong or can expect a problem..

What I did is create new tables linked to the Database... they came up with the same names, except suffixed with a 1 to indicate a different table...

Then I deleted the original table and renamed the suffixed one by removing the 1. The query still shows the same table name... Does this mean I am safe and the query will now use the new Live database tables? Or will I have an issue when I run the query?

 

[SOS]

You can also just use the Linked Table Manager to change the source of the linked tables.

 

[NBVC]

I did try that, but didn't see where I could change it.

I selected a table in the manager, clicked OK and it seems it only refreshed the tables.. could not figure out how to change the source.

 

[SOS]

I did try that, but didn't see where I could change it.

I selected a table in the manager, clicked OK and it seems it only refreshed the tables.. could not figure out how to change the source.

You have to check the box at the bottom left (Always Prompt For New Location).

 

[NBVC]

Ah, that is what that means... as homer would say... d'oh!

Thanks very much.

 

[Simon_MT]

Another way is in the Properties of the Query replace the Table to New Name and change in the FROM statement with SQL View.

FROM Table1 AS Table

Table1 is aliases to Table so you remove the alias.

Simon