SQL Server 2005

[Brian]

I am planning on rewriting an Access database using an Access front/back ends
into one that uses an Access front-end and SQL Server 2005 back end. When I
designed the db originally, the database was stored on the G:\ drive and the
front end was on each computer. On my development computer, my G:\ drive
pointed to a different location, so I was always using a duplicate set of
data for development. When the db was rolled out to the users, the link
still pointed to the G:\ drive, but because it was mapped differently, they
used the live data.

Anyway, now that the back end is SQL and the tables are linked into the
front-end Access db, how do I make sure that I am using a duplicate set of
data, without unlinking and relinking all of the tables every time I roll out
an update?

Have I stated this clearly enough?

Thanks for anyone who can offer suggestions.

Brian

 

[Nikos Yannacopoulos]

Brian,

You can re-link programatically upon opening the FE, so using some kind
of check (manual selection? Windows logon? local machine name?...) can
lead to connecting to a different BE.

One technique I have used repeatedly is, keep a local table in the FE,
with the names of the tables in the BE, and run some code at startup to
open the table as a recordset, loop through it to read the BE table
names, and link in code; then unlink at closing. That can be easily
modified to work nicely with some check as suggested above, and link to
the pertinent BE every time.

HTH,
Nikos