Multiple Access and ODBC connections

[Macsmasher]

Hi all,

We're running Access 2003, SQL 2005.

We have internal Access front-end apps that are distributed only within our
network. All workstations have the same network path, so there is no need
for linking tables at the client end. What I need to do is put together
something for our developers where we can change from our test server to live
server when we deploy a new release. Of course I've programmatically done
this with DAO in the past to a single Access back-end. And I can find code
to re-link to SQL server via ODBC. But I’m not quite sure how to put
together a solution for our needs. Here are some specifics...

We are linked to test SQL and Access DBs, and developers change the links to
live SQL and Access DBs right before deployment. Client never needs to link
tables.

We have multiple Access back-ends, but not all need to be re-linked. IOW,
we have some common lookup tables that always live on the live server even
when we're linked to test data.

We use ODBC connections to SQL using Windows username and password. Only
one server is used for test, and one used for live.

I'm thinking it would be best to have a table in the front-end with link
strings and a field to designate the connection as Test or Live. We would
have a drop-down on our developer admin form to select Test or Live, then run
the re-link procedure with a cmd button.

Anyway, thanks in advance for any input.

 

[sfisher973]

Look up "Startup command line options" in Access Help you can create a short
cut and either pass a "command" or the name of a macro to execute to access.
So using the "Development" shortcut runs the code to link to the Test DBs,
and using the "Production" Shortcut runs the code to link to the Production
DBs.

 

[david]

Yes, that sounds good. Let me warn you against using a DSN instead
-- that often causes problems.

There are several examples of table-relinkers on the Access Web.
One of them may be table-based: if not, have a look around some
more, because I am sure that there are table-based examples.

Any way you do it will probably work, but in the end we defaulted
to creating a new table link for each table, then deleting the original
link, then re-naming the new link. "Refreshing" an existing link
works ok for MDB links, but can be a problem for ODBC links,
because of the ODBC link caching.

We only used the table to handle exceptions - tables that had been
renamed, links to odd databases, names that were not legal in particular
server releases, stuff like that. Apart from that, we just automatically
changed everything linked to Test to linked to Production: Either it
was an exception, or it got switched using the default algorithm.

Because there were a lot of tables involved, I also ran a test on each
new link to check that the primary key had linked correctly, and
took a guess at the key field if it had failed. That was more robust
than depending on people to do a design check every that they
made a table design change.

(david)

 

[Database Whiz Consulting]

Hey Macsmasher. We do this in EVERY one of our apps. We have a table called
Version in our apps. One of the fields is a boolean called InProduction.
While in development, this is false. When we deploy, it is true. On startup,
each app runs this function or some variation of it:

Public Function UpdateTables() As Boolean
Dim tbl As TableDef

On Error GoTo UpdateTables_Err

'step through all tables in this database
For Each tbl In CurrentDb.TableDefs

'if table is a linked table,
If tbl.Connect <> "" Then
If DLookup("InProduction", "Version") Then
tbl.Connect = <USE PRODUCTION CONNECTION STRING>
Else
tbl.Connect = <USE DEVELOPMENT CONNECTION STRING>
End If
tbl.RefreshLink
End If
Next

UpdateTables = True
Exit Function

UpdateTables_Err:
Err.Clear
UpdateTables = False
End Function


Couple of notes though:

1) If you are using Named Pipes to connect, you actually only have to run
the function once before you deploy the app. It doesn't need to run each time
the app opens, but it doesn't hurt it if it did.

2) Refreshing table links programmatically removes the primary keys off of
the views. Depending on how your app is using the views (continuous form?),
this can cause SQL to lock large blocks of data, so only refresh the links on
startup if you have to OR exclude the views from refreshing. With Named
Pipes, this is not a problem. If you are connecting with the TCP/IP library,
however, it is because each table and view has to be reconnected each time
the app opens.


Hope this helps.


Ray Jefferson
Database Whiz Consulting
Henderson, NV 89074