Programmatically Update ODBC Linked Objects

[Joe at NC State]

We have dozens of MS Access databases on our shared network space, each
of which contains a number of objects linked to various Sybase
databases. Because our IT people are in the process of replacing three
of their Sybase servers, we've had to change a number of our ODBC data
sources to reflect the new server names. The old data sources will be
no good after the existing servers are retired in the next month or so.

Bottom line, to keep from having to manually relink every Sybase object
in every Access database stored on our network (of which there are
literally dozens), we really need to create some type of module or
function that for a given database would look through all Sybase ODBC
linked tables for any occurrences of this:

ODBC;DSN=[old DSN name];SRVR=[old Sybase server name];

....and replace each one with this:

ODBC;DSN=[new DSN name];SRVR=[new Sybase server name];

The rest of the connection string for each object (i.e., the "DB" and
"TABLE" names) would remain the same for every linked object, as only
the server name & DSN name are changing. These databases have all
kinds of links to all kinds of objects (Sybase, SQL Server, other
Access databases, and sometimes even Excel and/or text files), but only
the specific Sybase data sources that I would specify in the code would
need to be affected. Does that sound doable?

Incidentally, I've already tried to use Dev Ashish's code for "Relink
ODBC tables from code" found in The Access Web site (at
http://www.mvps.org/access/tables/tbl0010.htm), but it didn't work for
me. It seems to work only if your linked objects are SQL Server, not
Sybase. There's a similar solution on Microsoft's website (ACC2000:
Procedure to Create Data Sources and Relink ODBC Tables - article
#208295 dated 6/24/04), but like Dev Ashish's code, it works only on
SQL Server objects (it says so). I even tried changing one of the
parameters on the "DBEngine.RegisterDatabase" subroutine from "SQL
Server" to "Sybase ASE ODBC Driver" (the driver we use for our Sybase
data sources), but that didn't work either. I also tried an alternate
solution by Dev Ashish, some code called "fRefreshLinks" also found in
The Access Web site (at tbl0009.htm, same URL cited above), but that
didn't work either -- it wouldn't really do anything.

The alternative (i.e., doing the "manual thing" described above) is
extremely tedious, time-consuming, and leaves much room for error. Our
time frame is less than a month. Whatever advice or direction anyone
could give would be greatly appreciated.

 

[Douglas J. Steele]

What was the problem with Dev's code? Did you get an error message?

You'd only need RegisterDatabase if the DSNs don't already exist.

The code in http://www.mvps.org/access/tables/tbl0009.htm definitely isn't
appropriate: it's only for linked Jet tables (i.e.: links to another MDB
file)

 

[Joe at NC State]

Since I had tried so many different things, I decided to start all over
with a brand new Access database. I linked several objects using the
"old" DSN's that are going to be replaced. I created the
"tblReconnectODBC" table as suggested in the code and populated it with
the appropriate values related to the "new" DSN's (which, by the way,
already exist in the registry). I then pasted Dev's code from the
webpage into a new module and compiled it (no problems there).
Finally, when I issued the command "?fReconnectODBC()" from the
immediate window, I got the following error message: "The User DSN for
Oracle Tables were not found. Please check ODBC32 under Control
Panel." I'm not sure why I got this error because the DSN's already
exist. Now for all of our users, the DSN's are SYSTEM and not USER
DSN's, and we're talking about Sybase rather than Oracle, but I don't
think that makes any difference. Forgive my ignorance in advance if
I'm missing something obvious. Any suggestions?

 

[Douglas J. Steele]

System DSNs will be found under the HKEY_LOCAL_MACHINE hive in the registry.
Change the references to HKEY_CURRENT_USER to HKEY_LOCAL_MACHINE, and you
should be okay.

(I'll pass along a suggestion to Dev and Arvin to update that page to
reflect this fact!)

 

[Joe at NC State]

I tried that, but it did not work; I got the same error message. I
replaced the references to HKEY_CURRENT_USER with HKEY_LOCAL_MACHINE,
but only in the function part of the code (i.e., lines 117 and 122).
Here are two lines in the declarations section of the code (lines
26-27):

Private Const HKEY_CURRENT_USER = &H80000001
Private Const HKEY_LOCAL_MACHINE = &H80000002

The first time when I did the search & replace, I wasn't paying
attention to these lines and got an ambiguous reference error because
of this:

Private Const HKEY_LOCAL_MACHINE = &H80000001
Private Const HKEY_LOCAL_MACHINE = &H80000002

So I put the code back the way it was & simply remmed out the first
line of the pair like this:

' Private Const HKEY_CURRENT_USER = &H80000001
Private Const HKEY_LOCAL_MACHINE = &H80000002

That didn't work either (yes, I remembered to save & compile); I still
got the same error message about not being able to find the user DSN.
So I tried remming out both original lines and referencing local
machine as "&H80000001" instead of "&H80000001", like this:

' Private Const HKEY_CURRENT_USER = &H80000001
' Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Const HKEY_LOCAL_MACHINE = &H80000001

Unfortunately, that didn't work either; same message. And, by the way,
I don't know if this makes any difference or not, but after I click OK
to get rid of the message box, I go back to the VB window and a value
of "False" is sitting there. What else should I try?

 

[Douglas J. Steele]

The fact that the error message is unchanged doesn't mean anything: the
wording comes from the error handler. You definitely want

Private Const HKEY_LOCAL_MACHINE = &H80000002

If you're sure that the DSNs exist, simply remove the code that's checking
for their existence:

'Check to make sure ODBC DSNs are present
'You can follow the same steps to check for multiple DSNs
strTmp = fReturnRegKeyValue(HKEY_CURRENT_USER, _
cREG_PATH & "\qc03", "Server")
If strTmp = vbNullString Then Err.Raise cERR_NODSN

'Another ODBC DSN
strTmp = fReturnRegKeyValue(HKEY_CURRENT_USER, _
cREG_PATH & "\PMIP", "Server")
If strTmp = vbNullString Then Err.Raise cERR_NODSN

You do realize, I hope, the qc03 and PMIP above are DSN names: you'll have
to change them as appropriate for your case. The problem, though, is
probably that perhaps whatever ODBC driver you're using (Oracle, I think it
was) doesn't place a key named Server in the registry. Open the registry,
find the DSNs and find an appropriate key to look for.

 

[Joe at NC State]

OK, I think I'm getting somewhere. I did what you suggested; i.e.,
unremming the code in the declarations section and remming out the
section in the code where it's checking for the existence of DSN's.
After saving & compiling, I tried again & it appeared to run OK at
first (i.e., I got the confirmation message box asking me if it was OK
to drop & relink ODBC links). As soon as I clicked YES on the
confirmation, this is what happened:

VBA Errors in reconnect
Error#: 3070
The Microsoft Jet database engine does not recognize 'TableName' as a
valid
field name or expression.
OK

As soon as I clicked OK on the above dialog box, it sort of went into
an endless loop showing me the following:

VBA Errors in reconnect
Error#: 10
This array is fixed or temporarily locked
OK

No matter how many times you click OK, it just keeps repeating
endlessly with a message in the status bar: "Storing ODBC link info..."

I looked more closely & figured out that the code was looking for a
column name called "TableName" in the "tblReconnectODBC" table, but the
explanation in the webpage tells you to call the column
"LocalTableName" -- I fixed that (renamed the column) & ran the code
again. Here's what happened:

Again I got the confirmation message at the outset & clicked OK. But
this time, here's the error:

VBA Errors in reconnect
Error #: 3265
Item not found in this collection.
OK

After clicking OK, it goes into that same infinite loop I described
above (Error #10). It looks like it wants to reconnect every single
ODBC link in the database & not just the ones specified in the
"tblReconnectODBC" table. And (as it turns out) we want it to refresh
only the objects specified in the table. I just need to figure out a
way to do that.

Even better would be for the module to use a table with two columns:
OldConnectString and NewConnectString. It would then loop through all
objects in the database linked using OldConnectString and relink them
using the value in NewConnectString. Now THAT would indeed be the holy
grail for us.

Any thoughts?

 

[Joe at NC State]

UPDATE: I tried a different approach and it seemed to work; the
procedure looped through all the linked tables listed in the control
table (which I modified to have only a list of linked table names) and
successfully relinked them all.

Unfortunately, it did not preserve the indexes that were set when the
tables were originally linked. I need to be able to preserve the
index. Even better would be a way to do this same thing without having
to employ a control table listing all the linked objects. That is, a
way to loop through all linked objects of a certain type and perform
the same replace operation. I actually tried to do that on my 2nd
attempt by adding ".Properties.Refresh" and ".Indexes.Refresh" to the
code, but that didn't work (as documented below).

Here's an example of the code I used (adapted from a microsoft support
article): (stuff surrounded by brackets are generic things I'm
substituting for the "real" server names, by the way)

'*******************************************************
' code adapted from microsoft by jwp
'*******************************************************
Function ChangeLink()
'adapted from
http://support.microsoft.com/default.aspx?scid=kb;en-us;172347
'need table containing dsn names to loop through
'remmed out lines not needed
'Function ChangeLink(strLinkName As String, strDSNName As String, _
' Optional IsFileDSN As Boolean)

Dim db As Database, tdf As TableDef
Dim rs As Recordset
' changed from original microsoft code
' Dim strConn As String
Dim strConn As Variant
' originally added these variables -- but they are not needed
' Dim strLinkName As Variant
' Dim strDSNName As Variant
' Dim strNewDSNName As Variant

On Error GoTo Errorhandler
Set db = CurrentDb

' open table containing dsn names and linked table names
Set rs = db.OpenRecordset("tblReconnect", dbOpenSnapshot)

' now start looping through records in tblreconnect
With rs
.MoveFirst
Do While Not .EOF
strLinkName = rs!tablename
' originally included these variables -- no longer needed
' strDSNName = rs!OldConnectString
' strNewDSNName = rs!NewConnectString
strConn = db.TableDefs(strLinkName).Connect

' now do the search/replace -- only DSN part and SRVR part
' of connect string needs to change -- the TABLE part remains the same

' STEP ONE
' Find all occurrences of [1st old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name]", "ODBC;DSN=[new
name]")
End If

' STEP TWO
' Find all occurrences of [2nd old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name2]", "ODBC;DSN=[new
name2]")
End If

' STEP THREE
' Find all occurrences of [1st old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "SRVR=[old name]", "SRVR=[new name]")
End If

' STEP FOUR
' Find all occurrences of [2nd old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "SRVR=[old name2]", "SRVR=[new name2]")
End If

' Link with the new connect string.
With db.TableDefs(strLinkName)

.Connect = newstrConn
.RefreshLink
' added next 2 lines in an attempt to refresh table indexes
' and description -- but they did not work
.Properties.Refresh
.Indexes.Refresh

End With
' now loop to next record in control table
.MoveNext
Loop
End With

Exit Function

Errorhandler:

MsgBox Err & " " & Err.Description
Exit Function
'********************************************

That's it. Whatever advice or direction would be greatly appreciated.

 

[Douglas J. Steele]

Take a look at what I have at
http://www.accessmvp.com/djsteele/DSNLessLinks.html for how to generate the
indexes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE: I tried a different approach and it seemed to work; the
procedure looped through all the linked tables listed in the control
table (which I modified to have only a list of linked table names) and
successfully relinked them all.

Unfortunately, it did not preserve the indexes that were set when the
tables were originally linked. I need to be able to preserve the
index. Even better would be a way to do this same thing without having
to employ a control table listing all the linked objects. That is, a
way to loop through all linked objects of a certain type and perform
the same replace operation. I actually tried to do that on my 2nd
attempt by adding ".Properties.Refresh" and ".Indexes.Refresh" to the
code, but that didn't work (as documented below).

Here's an example of the code I used (adapted from a microsoft support
article): (stuff surrounded by brackets are generic things I'm
substituting for the "real" server names, by the way)

'*******************************************************
' code adapted from microsoft by jwp
'*******************************************************
Function ChangeLink()
'adapted from
http://support.microsoft.com/default.aspx?scid=kb;en-us;172347
'need table containing dsn names to loop through
'remmed out lines not needed
'Function ChangeLink(strLinkName As String, strDSNName As String, _
' Optional IsFileDSN As Boolean)

Dim db As Database, tdf As TableDef
Dim rs As Recordset
' changed from original microsoft code
' Dim strConn As String
Dim strConn As Variant
' originally added these variables -- but they are not needed
' Dim strLinkName As Variant
' Dim strDSNName As Variant
' Dim strNewDSNName As Variant

On Error GoTo Errorhandler
Set db = CurrentDb

' open table containing dsn names and linked table names
Set rs = db.OpenRecordset("tblReconnect", dbOpenSnapshot)

' now start looping through records in tblreconnect
With rs
.MoveFirst
Do While Not .EOF
strLinkName = rs!tablename
' originally included these variables -- no longer needed
' strDSNName = rs!OldConnectString
' strNewDSNName = rs!NewConnectString
strConn = db.TableDefs(strLinkName).Connect

' now do the search/replace -- only DSN part and SRVR part
' of connect string needs to change -- the TABLE part remains the same

' STEP ONE
' Find all occurrences of [1st old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name]", "ODBC;DSN=[new
name]")
End If

' STEP TWO
' Find all occurrences of [2nd old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name2]", "ODBC;DSN=[new
name2]")
End If

' STEP THREE
' Find all occurrences of [1st old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "SRVR=[old name]", "SRVR=[new name]")
End If

' STEP FOUR
' Find all occurrences of [2nd old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "SRVR=[old name2]", "SRVR=[new name2]")
End If

' Link with the new connect string.
With db.TableDefs(strLinkName)

.Connect = newstrConn
.RefreshLink
' added next 2 lines in an attempt to refresh table indexes
' and description -- but they did not work
.Properties.Refresh
.Indexes.Refresh

End With
' now loop to next record in control table
.MoveNext
Loop
End With

Exit Function

Errorhandler:

MsgBox Err & " " & Err.Description
Exit Function
'********************************************

That's it. Whatever advice or direction would be greatly appreciated.

 

[Joe at NC State]

Thanks for your help; it looks like your suggestion may work.

 

[Tasha]

Hi Doug,

I'm having a similar issue and it looks like the link below may help. I have
a question though. What is a trusted connection? Does it use your windows
logon to validate that you have access to the SQL server backend?

(I know I should normally start my own post but I thought this would be very
related)

Thanks for the help!
Tasha

Take a look at what I have at
http://www.accessmvp.com/djsteele/DSNLessLinks.html for how to generate the
indexes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE: I tried a different approach and it seemed to work; the
procedure looped through all the linked tables listed in the control
table (which I modified to have only a list of linked table names) and
successfully relinked them all.

Unfortunately, it did not preserve the indexes that were set when the
tables were originally linked. I need to be able to preserve the
index. Even better would be a way to do this same thing without having
to employ a control table listing all the linked objects. That is, a
way to loop through all linked objects of a certain type and perform
the same replace operation. I actually tried to do that on my 2nd
attempt by adding ".Properties.Refresh" and ".Indexes.Refresh" to the
code, but that didn't work (as documented below).

Here's an example of the code I used (adapted from a microsoft support
article): (stuff surrounded by brackets are generic things I'm
substituting for the "real" server names, by the way)

'*******************************************************
' code adapted from microsoft by jwp
'*******************************************************
Function ChangeLink()
'adapted from
http://support.microsoft.com/default.aspx?scid=kb;en-us;172347
'need table containing dsn names to loop through
'remmed out lines not needed
'Function ChangeLink(strLinkName As String, strDSNName As String, _
' Optional IsFileDSN As Boolean)

Dim db As Database, tdf As TableDef
Dim rs As Recordset
' changed from original microsoft code
' Dim strConn As String
Dim strConn As Variant
' originally added these variables -- but they are not needed
' Dim strLinkName As Variant
' Dim strDSNName As Variant
' Dim strNewDSNName As Variant

On Error GoTo Errorhandler
Set db = CurrentDb

' open table containing dsn names and linked table names
Set rs = db.OpenRecordset("tblReconnect", dbOpenSnapshot)

' now start looping through records in tblreconnect
With rs
.MoveFirst
Do While Not .EOF
strLinkName = rs!tablename
' originally included these variables -- no longer needed
' strDSNName = rs!OldConnectString
' strNewDSNName = rs!NewConnectString
strConn = db.TableDefs(strLinkName).Connect

' now do the search/replace -- only DSN part and SRVR part
' of connect string needs to change -- the TABLE part remains the same

' STEP ONE
' Find all occurrences of [1st old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name]", "ODBC;DSN=[new
name]")
End If

' STEP TWO
' Find all occurrences of [2nd old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name2]", "ODBC;DSN=[new
name2]")
End If

' STEP THREE
' Find all occurrences of [1st old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "SRVR=[old name]", "SRVR=[new name]")
End If

' STEP FOUR
' Find all occurrences of [2nd old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "SRVR=[old name2]", "SRVR=[new name2]")
End If

' Link with the new connect string.
With db.TableDefs(strLinkName)

.Connect = newstrConn
.RefreshLink
' added next 2 lines in an attempt to refresh table indexes
' and description -- but they did not work
.Properties.Refresh
.Indexes.Refresh

End With
' now loop to next record in control table
.MoveNext
Loop
End With

Exit Function

Errorhandler:

MsgBox Err & " " & Err.Description
Exit Function
'********************************************

That's it. Whatever advice or direction would be greatly appreciated.

 

[Douglas J. Steele]

Yes, you should have started your own post...

As you suspected, Trusted Connection uses the Windows credentials to
authenticate the user, so that no additional ID need be set up in SQL
Server.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

I'm having a similar issue and it looks like the link below may help. I
have
a question though. What is a trusted connection? Does it use your windows
logon to validate that you have access to the SQL server backend?

(I know I should normally start my own post but I thought this would be
very
related)

Thanks for the help!
Tasha

Take a look at what I have at
http://www.accessmvp.com/djsteele/DSNLessLinks.html for how to generate
the
indexes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE: I tried a different approach and it seemed to work; the
procedure looped through all the linked tables listed in the control
table (which I modified to have only a list of linked table names) and
successfully relinked them all.

Unfortunately, it did not preserve the indexes that were set when the
tables were originally linked. I need to be able to preserve the
index. Even better would be a way to do this same thing without having
to employ a control table listing all the linked objects. That is, a
way to loop through all linked objects of a certain type and perform
the same replace operation. I actually tried to do that on my 2nd
attempt by adding ".Properties.Refresh" and ".Indexes.Refresh" to the
code, but that didn't work (as documented below).

Here's an example of the code I used (adapted from a microsoft support
article): (stuff surrounded by brackets are generic things I'm
substituting for the "real" server names, by the way)

'*******************************************************
' code adapted from microsoft by jwp
'*******************************************************
Function ChangeLink()
'adapted from
http://support.microsoft.com/default.aspx?scid=kb;en-us;172347
'need table containing dsn names to loop through
'remmed out lines not needed
'Function ChangeLink(strLinkName As String, strDSNName As String, _
' Optional IsFileDSN As Boolean)

Dim db As Database, tdf As TableDef
Dim rs As Recordset
' changed from original microsoft code
' Dim strConn As String
Dim strConn As Variant
' originally added these variables -- but they are not needed
' Dim strLinkName As Variant
' Dim strDSNName As Variant
' Dim strNewDSNName As Variant

On Error GoTo Errorhandler
Set db = CurrentDb

' open table containing dsn names and linked table names
Set rs = db.OpenRecordset("tblReconnect", dbOpenSnapshot)

' now start looping through records in tblreconnect
With rs
.MoveFirst
Do While Not .EOF
strLinkName = rs!tablename
' originally included these variables -- no longer needed
' strDSNName = rs!OldConnectString
' strNewDSNName = rs!NewConnectString
strConn = db.TableDefs(strLinkName).Connect

' now do the search/replace -- only DSN part and SRVR part
' of connect string needs to change -- the TABLE part remains the same

' STEP ONE
' Find all occurrences of [1st old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name]", "ODBC;DSN=[new
name]")
End If

' STEP TWO
' Find all occurrences of [2nd old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name2]", "ODBC;DSN=[new
name2]")
End If

' STEP THREE
' Find all occurrences of [1st old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "SRVR=[old name]", "SRVR=[new name]")
End If

' STEP FOUR
' Find all occurrences of [2nd old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "SRVR=[old name2]", "SRVR=[new name2]")
End If

' Link with the new connect string.
With db.TableDefs(strLinkName)

.Connect = newstrConn
.RefreshLink
' added next 2 lines in an attempt to refresh table indexes
' and description -- but they did not work
.Properties.Refresh
.Indexes.Refresh

End With
' now loop to next record in control table
.MoveNext
Loop
End With

Exit Function

Errorhandler:

MsgBox Err & " " & Err.Description
Exit Function
'********************************************

That's it. Whatever advice or direction would be greatly appreciated.