refreshing links in ADO

[rewdee]

Good Morning,

I am in the process of converting some DAO code to ADO. I have a routine that refreshes links as below:

Function RefreshLinks() As Boolean
Dim sDBPath As String
Dim strMsg As String
Dim cat As New ADOX.Catalog
Dim tdfLocal As New ADOX.Table
 
Const cFILE_NOT_FOUND = vbObjectError + 1000
 
    sDBPath = getBackendDB
    cat.ActiveConnection = CurrentProject.Connection
 
    On Local Error GoTo RefreshLinks_Err
    
    'first make sure we have a valid path to remote db
    If Len(Dir(sDBPath)) = 0 Then Err.Raise cFILE_NOT_FOUND
   
    'iterate through looking for linked tables
    For Each tdfLocal In cat.Tables
        If tdfLocal.Type = "LINK" Then
            tdfLocal.Properties("JET OLEDB:Create Link") = False
            tdfLocal.Properties("JET OLEDB:Link Data Source") = sDBPath
            tdfLocal.Properties("JET OLEDB:Create Link") = True
        End If
    Next
    
    RefreshLinks = True
    
RefreshLinks_End:
    Set tdfLocal = Nothing
    Set cat = Nothing
    Exit Function
RefreshLinks_Err:
    RefreshLinks = False
    Select Case Err
        Case 3059:
 
        Case cFILE_NOT_FOUND:
            LogMessage ("The Path to file: " & sDBPath & ", couldn't link tables.")
        
        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: RefreshLinks" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            LogMessage strMsg
    End Select
    Resume RefreshLinks_End
End Function

The strange thing is that when executing the function the code gets to:

tdfLocal.Properties("JET OLEDB:Create Link") = False

an error is raised but the error description is "".

Can anyone see what is the error?

Thanks,
Rewdee

P.s. Maybe the error has something to do with I'm writing this code in AccessXP and the backend database is Access 97.

 

[rewdee]

oops the error that I'm getting is :

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Does that help anyone?

Thanks,
Rewdee

 

[cmmrfrds]

An example at this url.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q291264&LN=EN-US

Are you sure this is NOT a read only property?
tdfLocal.Properties("JET OLEDB:Create Link&quot = False

 

[rewdee]

Thanks cmmrfrds. You are correct. It appears that the Create Link property of the table catalog is either Read Only or invalid.

What is interesting is that I got the code from the VB project on the MS site

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

that anyone can download. I've done some minor modifications but the

If tdfLocal.Type = "LINK" Then
  tdfLocal.Properties("JET OLEDB:Create Link") = False
  tdfLocal.Properties("JET OLEDB:Link Data Source") = sDBPath
  tdfLocal.Properties("JET OLEDB:Create Link") = True
End If

is right from the VB code.

Anyway, it's fixed. Thanks again,
Rewdee