Dealing with SQL server back ends

[jerryk]

Hi,

We have an Access app that we are moving to an SQL Server backend. We have
the code working but one mantaining the location of the database is becoming
a pain. We have modules, query definitions (Pass-through), and table
definitions that all need to keep track of the location and connection
information for the SQL Server DB. As we move between development to
production SQL Servers and back we have update them with difference
mechanisms, code strings, update query defs, link manager, etc. Can anyone
suggest a better way to keep these paths updated?

Thanks,

Jerry

 

[Tony Toews]

We have an Access app that we are moving to an SQL Server backend. We have
the code working but one mantaining the location of the database is becoming
a pain. We have modules, query definitions (Pass-through), and table
definitions that all need to keep track of the location and connection
information for the SQL Server DB. As we move between development to
production SQL Servers and back we have update them with difference
mechanisms, code strings, update query defs, link manager, etc. Can anyone
suggest a better way to keep these paths updated?

I use the code as found on the Access Web at
http://www.mvps.org/access/tables/tbl0009.htm to relink the tables
with lots of additions.

I keep the paths in a file in each users front end location. I use
an INI file to be specific but the type of data storage mechanism
doesn't matter significantly. You can't store this info in the FE
as a new FE would wipe this out. You also can't store this info on
the server as the app wouldn't know where to find it. You could also
use the registry or an MDB.

Should a new FE be distributed to the user
http://www.granite.ab.ca/access/autofe.htm I check the location of the
current linked backend against the stored location. If different
then I relink all the tables

I also open a recordset against the first linked table just to ensure
that the backend does exist where expected.

I also display the path on some main screens possibly along with the
word TEST. I've also been know to change the main menu to a dark red
when I'm using the live backend so I don't make any dangerous changes.

As far as keeping the SQL Server production database updated with the
changes made to the development database I'd ask in the SQL Server
newsgroup.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm