Automatically Link Tables on Startup 2

[jmeckley]

I am attempting to refresh the tables when the users start the program everyday. the application is an Access 2000 application with a SQL Server backend. So far i have the code below on the onload event.

Dim CNN As ADODB.Connection
Dim CAT As ADOX.Catalog
Dim TBL As ADOX.Table

Set CNN = CurrentProject.Connection
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CNN

For Each TBL In CAT.Tables
TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=Bar Examiners;Database=Trainging2"
Next

however, when i execute this code i recieve the following error message:

Run-time error '-2147467259(80004005)':
ODBC--connection to 'Bar Examiners' failed.

There seems to be endless possiblities as to why this didn't work, any idea?

Jason

 

[FancyPrairie]

Just a guess...But it looks like you have Trainging2 spelled wrong. I assume it should be Training2

 

[jmeckley]

FancyPrairie

Thank you for your keen eye. it's usually the little things that get away from us.

Now that i have the correct spelling of the database I have another issue. the same error number appears with a new error message:

"The object is invalid for operation"

The error occurs in the "For, Next" line of code.

For Each TBL In CAT.Tables
TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=Bar Examiners;Database=Training2"
Next

I have never worked with this type of procedure before so I don't know where to go from here.

Jason

 

[FancyPrairie]

Here's some code I have not had time to test, but should be right. I'm not sure, but I think you need the semicolon at the end of your string (i.e. TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=Bar Examiners;Database=Training2;&quot.

    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cnn = CurrentProject.Connection
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn

    For Each tbl In cat.Tables

'***************************************************************************
'*  LINK:  Indicates table is linked to another Microsoft Access Database  *
'***************************************************************************

        If (tbl.Type = "LINK") Then
        
            tbl.Properties("Jet OLEDB:Link Datasource") = "NameOfAccessDataBase"

'***********************************************************************
'*  PASS-THROUGH:  Indicates table is linked to a SQL server database  *
'***********************************************************************

        ElseIf (tbl.Type = "PASS-THROUGH") Then
        
            tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=YourDSNname;DATABASE=YourDatabaseName;UID=YourUserName;pwd=YourPassword;"
    
        End If

    Next

 

[jmeckley]

FancyPraire

I do not have any tables linked to another access database, I removed the "If Then" statement from the code to increase processing time.

I created a counter within the "For Next" loop.

Dim I As Integer
For Each TBL In CAT.Tables
Debug.Print I
TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=Bar Examiners;Database=Training2;"
I = I + 1
Next

The Immdiate Window prints out the following information
0
1
2
3

This tells me it is running the code 3 times and then failing. does this mean only the first 3 tables are relinked, and that it crashed on the fourth relink and no other tables are updated?

If this is true, is the error within the code, or the network connection, or the table structure?

jason

 

[FancyPrairie]

I would say that the first 4 tables have been relinked (check them via TOOLS|DATABASE UTILITIES|LINK TABLE MANAGER). Also, in your loop, insert "Debug.print tbl.name" to see what tables are being relinked. Finally, include an On Error statement to trap any errors you encounter.

One more thing, does the 5th table reside on the SQL server? If not, that may explain why it aborts after the 4th table is relinked.

 

[jmeckley]

FancyPrairie

Thank you for your advice. it has been tremendously helpful. I now know what the "object not valid error" means.

for some reason the code is attempting to relink queries. Do you have any idea why this might be?

jason

 

[jmeckley]

FancyPrarie

I figured out my dilema. I needed the "IF Then" statement after all. I needed to only link the PASS-THROUGH tables.

thank you for you assistance. the code is posted below for anyone who may need it:

On Error GoTo Error_Form_Load

Dim CNN As ADODB.Connection
Dim CAT As ADOX.Catalog
Dim TBL As ADOX.Table

Set CNN = CurrentProject.Connection
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = CNN

For Each TBL In CAT.Tables
If TBL.Type = "PASS-THROUGH" Then
TBL.Properties("Jet OLEDB:Link Provider String&quot = "ODBC;DSN=Bar Examiners;Database=Training2;"
End If
Next TBL

Exit_Form_Load:
Exit Sub

Error_Form_Load:
MsgBox "There was an error linking table " & TBL.Name & "." & Chr(13) & "For further assitance contact the DBA."
Resume Exit_Form_Load