Updating Link to Linked Table through ADO Balks

[MichaelR]

I have a commercial program that outputs data in Access format. I have an
Access DB, call it the Library DB, that I have built lots of analysis code in
that will link to the output DB tables after each run. The table names in
the output file change with a version number like: Resource_Table_<version>
(ex: Resource_Table_1 then Resource Table_2 for the next run).

I have code that will search the Tables() to find the correct table in the
output db to link to in the library db. To effect the link change I need to
change the path & file the link points to and then the table name the link is
to. When I go to the table definition for the link table in the library db
and update the path to the new output db it tries to read the new output db
for the old table name and throws and error. Because the table names are
different in each output db it cannot find the old table name in teh new
output db. If I reverse the order and change the table name first it looks
for the new table name in the old output db.

If an error is thrown when a table property is being changed the change does
NOT take effect. Otherwise I would simply catch the error, resume next, and
there would be no problem

Is there a way to turn off the refresh of table definitions untill I have
finished changing all the properties in a table definition? If I could
control when the refresh happens that would solve the problem...

<code
'*************************************************************************************
'Find the target DB and re-link the target's data tables to the library's
tables
'
'Args:
'strTarget string The target DB that we are linking to
'cnnLib connection The library that holds all the workhorse queries and
reference
' tables.
'
'Returns:
'FALSE unsuccessful link
'TRUE all went well
'
'Date Init. Modifications
'04/08 MTR Starte
'*************************************************************************************

Private Function ReLinkBridgeLibrary(strTarget As String, cnnLib As
ADODB.Connection) As Boolean
'Dim cnnTgt As New ADODB.Connection, cnnLib As New ADODB.Connection
Dim catLib As New ADOX.Catalog 'The library's catalog where
table defs are kept
Dim itr As Integer, itrProp As Integer
Dim strCondTbl As String 'name of the input conditions
tbl in the tgt db

On Error GoTo Err_ReLinkBridgeLibrary
ReLinkBridgeLibrary = False

Set catLib.ActiveConnection = cnnLib
For itr = 0 To catLib.Tables.Count - 1

'Change only 'LINK' tables
If catLib.Tables(itr).Type = "LINK" Then
If Trim(catLib.Tables(itr).Name) = "zzzMap Conditions" Then

'Go access the target db and find the target's
_Input_Conditions_<version> and return it through strCondTbl. FALSE = not
found
If Not (FindConditionTbl(strTarget, strCondTbl)) Then
MsgBox "Input Conditions table not found in target db: " &
vbCrLf & vbCrLf _
& strTarget, vbCritical, "Input Conditions Not Fopund"
GoTo Exit_ReLinkBridgeLibrary
End If

'change the definition properties in the Library DB link table to
link to new _Input_Conditions_<version> table
catLib.Tables(itr).Properties("Jet OLEDB:Link Datasource") =
Trim(CStr(strTarget))
catLib.Tables(itr).Properties("Jet OLEDB:Remote Table Name") =
Trim(strCondTbl)
catLib.Tables(itr).Properties("Jet OLEDB:Create Link") = True
Else
catLib.Tables(itr).Properties("Jet OLEDB:Link Datasource") =
Trim(CStr(strTarget))
catLib.Tables(itr).Properties("Jet OLEDB:Create Link") = True
End If
End If
End If
Next itr

DoEvents
'Refresh the tables
catLib.Tables().Refresh
DoEvents

ReLinkBridgeLibrary = True

Exit_ReLinkBridgeLibrary:

'attempt to catch and ignore errors to see if that worked
If Err.Number = -2147467259 Or Err.Number = -2147217887 Then
Error 0
ReLinkBridgeLibrary = True
End If

Set catLib = Nothing
Exit Function

Err_ReLinkBridgeLibrary:
'attempt to catch and ignore errors to see if that worked
Select Case Err.Number
Case -2147467259
Resume Next
Case -2147217887
Resume Next
Case Else
MsgBox "There was an error re-linking:" & vbCrLf _
& "Target DB: " & strTarget & vbCrLf _
& "Library DB: " & gstrBridgeTemplatesDir & gstrBridgeLib & vbCrLf _
& Err.Number & ": " & Err.Description, _
vbCritical, "Error"

Resume Exit_ReLinkBridgeLibrary
End Select
End Function

</code>

Thanks in advance for your help!

Best regards,

Michael

 

[Douglas J. Steele]

You'd probably be better off deleting the old linked table and adding a new
one. As well, I think you'd be better off using DAO for this, not ADOX.

 

[MichaelR]

Thanks for direction, Doug. I actually implemented that route after having a
coffee break and trying to think of alternative solutions It works like a
champ now.

I'm using ADO as I am manipulating it all from XL and while we use Access
for now we have plans to migrate to SQL SRVR in the future. I figure, ADO is
supposed to be portable so I'll save myself some future hassle... I hope.

Best regards and have a nice weekend,

Michael