February 5, 2004 at 1:19 pm
We have development Sql server DB and user have creted lots of query in MS Access by pointing an ODBC(system DSN) to this dev. server, we now want to migrate this DB into production sql server , but wants to make sure that none of our user communiity get affected in terms of the Access queries, means they should continue using there Access query but pointing to the new Production server.
Pls suggest me what are the various means to do it .
Thanks in advance
February 6, 2004 at 6:57 am
I use different DSN names for Dev, Tst, and Prod. For example: myDSN_Dev, myDSN_Tst, and myDSN_Prod. When an ACCESS.mdb is moved from one environment to the next, we have VBA code to re-link all tables and change the DSN connection properties for SQL PASS-Through Queries. You can accomplish the same thing using the MS ACCESS "Linked Table Manager" and then manually editting the PASS-Through queries.
February 6, 2004 at 7:09 am
You could simple use the linked table manager [Tools, Database Utilities] and check off the tables you are changing. In some cases, checking "Prompt for new location" will let you pick a completely new DSN, rather than refresh the existing DSN.
An additional approach is to separate your data access database from your user interface database. The DSN links all sit in the data access database, and you link to this based on the MDB name of the file from the user interface database. Rolling out to production, as long as the data access location is relatively defined, the users will automatically point to the database that points to production, rather than test. You can accomplish this also by renaming the MDB that points to production to be the one the user interface MDB looks for.
February 6, 2004 at 7:39 am
This sounds a good solution , I will give a shot .
Thanks !!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply