Bulk Change ODBC Link

[Joe Cletcher]

My MS Access 2000 front-end is using an ODBC connection to a SQL Server
database. I have a production SQL Server database and a development SQL
Server database with separate ODBC connections. I also have an Access
front-end for each SQL Server database.

When the development front-end is verified to be operating correctly, I
replace the production front-end with the development front-end and must
update to links from the development ODBC connection to the production ODBC
connection. There are many linked tables and I must respond to a request for
the new ODBC (production) connection.

Is there a was to only have to provide the new ODBC source once. I tried
modifying the MSysObjects.Connection field value but the system table is read
only.

 

[Rick Brandt]

My MS Access 2000 front-end is using an ODBC connection to a SQL
Server database. I have a production SQL Server database and a
development SQL Server database with separate ODBC connections. I
also have an Access front-end for each SQL Server database.

When the development front-end is verified to be operating correctly,
I replace the production front-end with the development front-end and
must update to links from the development ODBC connection to the
production ODBC connection. There are many linked tables and I must
respond to a request for the new ODBC (production) connection.

Is there a was to only have to provide the new ODBC source once. I
tried modifying the MSysObjects.Connection field value but the system
table is read only.

See if the code at this link helps you...
http://www.mvps.org/access/tables/tbl0010.htm

BUT...in my opinion you are pushing your rope instead of pulling it. The
biggest advantage of using an ODBC DSN is that you can make a single change to
that DSN in Control Panel to change what database it points at without changing
a thing in your file. That is what you should be doing.

Just point the DSN on your development machine at the development SQL Server
database and when you are finished you simply distribute the file to your users
because that same DSN on their PC points at the live SQL Server.

 

[Pieter Wijnen]

Create a Passthrough Query with the Correct Connection
You can then Create a Simple routine to update all the TableDefs Connection
Property

Partial Code:
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Qdef As DAO.QueryDef
Dim TDef As DAO.TableDef
Dim thQ As String
Dim SplitConn As Variant, SplitDetail As Variant
Dim i As Long

' .. initialize here

thQ = "SELECT NAME AS DBS" & VBA.vbCrLf & _
"FROM MSYSOBJECTS" & VBA.vbCrLf & _
"WHERE TYPE=4" & VBA.vbCrLf & _
"AND CONNECT Like '*DATABASE=*'" & VBA.vbCrLf & _
"AND NAME NOT LIKE '~TMP*'"
Set Rs = Db.OpenRecordset(thQ, DAO.dbOpenSnapshot)
SplitConn = Null
While Not Rs.EOF
SplitConn = (SplitConn + ";") & Rs.Fields(0).Value
Rs.MoveNext
Wend
Rs.Close: Set Rs = Nothing
SplitDetail = VBA.Split(SplitConn, ";")
'Access.SysCmd Access.acSysCmdInitMeter, "Relinking Tables",
UBound(SplitDetail) + 1
For i = 0 To UBound(SplitDetail)

Set TDef = Db.TableDefs(SplitDetail(i))

TDef.Connect = Qdef.Connect
TDef.RefreshLink
'Access.SysCmd Access.acSysCmdUpdateMeter, i
Set TDef = Nothing
Next 'i

HtH

Pieter

 

[Guest]

The linked table wizard doesn't normally require you to enter
the link information more than once.

If you have to enter the link information more than once into
the linked table wizard, it means that you have selected tables
linked to two different databases. The wizard asks you for each
table because it assumes that there is more than one target database

You can reduce that problem by selecting subsets of your
tables to link, so that there is only one database.

The only problem with the linked table manager is that it
requires a file DSN. If you use your own link manager, using
any of the available code samples, you can get rid of the DSN.

(david)

 

[Guest]

make a single change to that DSN in Control Panel to change

what database it points at without changing a thing in your file.

That works well enough to be dangerous, but it is a bad idea
to depend on switching the contents of a DSN.

Since access stores table meta-data in the links, unless you
re-link your linked table may not match your target database
tables.

Also, Access copies data from the DSN into link. Updating
the DSN does not trigger an update of the link data. Unless
you re-link, the data in the link can get out of sink with the
data in the DSN. Occasionally you will find that you have changed
something in the DSN, but you are still connected to the old
database.

(david)

 

[Rick Brandt]

That works well enough to be dangerous, but it is a bad idea
to depend on switching the contents of a DSN.

Since access stores table meta-data in the links, unless you
re-link your linked table may not match your target database
tables.

Also, Access copies data from the DSN into link. Updating
the DSN does not trigger an update of the link data. Unless
you re-link, the data in the link can get out of sink with the
data in the DSN. Occasionally you will find that you have changed
something in the DSN, but you are still connected to the old
database.

If that's true then that must be yet another "improvement" introduced in the
newer versions. I have never had any problems changing the DSN target in
Control Panel. In most cases I do this on machines where the production
database source is not physically available so if Access DID try to talk to that
when the DSN pointed otherwise I would get an error (I presume). That has never
happened.

I could possibly believe MS would do something this stupid regarding the
database name, but if they also copy the SERVER location into the links then
that is just absurd. What is the bloody point of using a DSN if the application
is not going to utilize its information? That would be like making local copies
of settings that should be coming from the system registry and then ignoring the
fact that the registry setting are later modified.