Change the Underlying Database

[Jim Conrady]

Folks, I have looked in Access help, and also done google searches on this
and have not found a match. I am hoping someone can help me.

I am using Access 2003, and am connected to a SQL Server 2005 back end
database. I have linked a table on that SQL DB and generated several queries
and reports based on that table.

There is about to be an update to the table adding some columns. To test
the update, a database has been put on the same SQL Server instance with a
slightly different DB name. I would like to be able to easily substitute the
new table for the old in my queries... The field/column names are all the
same. I am hoping someone is going to tell me how easy this is...

Please...

Regards,

Jim

 

[strive4peace]

Hi Jim,

it is easy if you use a table alias...

in the query design:

1. show properties
right-click on the table and choose Properties from the shortcut menu

2. define an Alias for the table
click on the fieldlist and, in the Properties window, define a short
alias -- use this in any references to the table.

when you want to change the query to run on another table, go to the SQL
view of the query and edit Tablename in the FROM clause

FROM [Tablename] AS alias


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Jim Conrady]

Crystal, it took me a couple of tries to get this right... I kept trying to
add the new DB. could not get it to work. I put it aside for a couple days,
then came back and simply followed your instructions. Worked great, first
time! And yes, it was easy!

Thanks. I appreciate your support. I was looking at copying all the
queries to notepad and changing names. That woulda been ugly!!!

Best Regards,

Jim
--
--------------
Jim Conrady

Hi Jim,

it is easy if you use a table alias...

in the query design:

1. show properties
right-click on the table and choose Properties from the shortcut menu

2. define an Alias for the table
click on the fieldlist and, in the Properties window, define a short
alias -- use this in any references to the table.

when you want to change the query to run on another table, go to the SQL
view of the query and edit Tablename in the FROM clause

FROM [Tablename] AS alias


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Folks, I have looked in Access help, and also done google searches on this
and have not found a match. I am hoping someone can help me.

I am using Access 2003, and am connected to a SQL Server 2005 back end
database. I have linked a table on that SQL DB and generated several queries
and reports based on that table.

There is about to be an update to the table adding some columns. To test
the update, a database has been put on the same SQL Server instance with a
slightly different DB name. I would like to be able to easily substitute the
new table for the old in my queries... The field/column names are all the
same. I am hoping someone is going to tell me how easy this is...

Please...

Regards,

Jim

 

[strive4peace]

wonderful! you're welcome, Jim happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*