Change the Underlying Database

  • Thread starter Thread starter Jim Conrady
  • Start date Start date
J

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
 
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 :)
*
 
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


strive4peace said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top