Well, I am still going crazy here. I've tried using
tbl.Properties("Jet OLEDB:Link Datasource") = cnn.ConnectionString
AND
tbl.Properties("Jet OLEDB:Link Provider String") = cnn.ConnectionString
but still have problems. My inexperience with ADO really became obvious to me when I realized I spent way too much time trying to refresh the links in the wrong direction (from SQL to Access instead of the other way).
My code currently appears as follows:
Code:
Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim cnn As New ADODB.Connection
Dim sConnString As String
Dim prp As ADOX.Property
Dim ADOerr As ADODB.Error
Dim i As Integer
Dim strerror As String
On Error GoTo OpenConnectionError
sConnString = "Driver={SQL Server};" & _
"Server=servername;" & _
"Database=mydatabase;" & _
"Uid=uname;" & _
"Pwd=password;"
cnn.Open sConnString
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
For Each tbl In cat.Tables
Debug.Print tbl.Name & " " & tbl.Type
If tbl.Type = "PASS-THROUGH" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
cat.Tables.Refresh
Exit Function
OpenConnectionError:
Set cat = Nothing
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
'Error trapping Code
For Each ADOerr In cnn.Errors
strerror = "Error #" & ADOerr.Number & vbCr & _
" " & ADOerr.Description & vbCr & _
" (Source: " & ADOerr.Source & ")" & vbCr & _
" (SQL State: " & ADOerr.SQLState & ")" & vbCr & _
" (NativeError: " & ADOerr.NativeError & ")" & vbCr
Debug.Print strerror
Next
Set cnn = Nothing
If Err <> 0 Then
Debug.Print Err.Description
Debug.Print Err.Number
MsgBox Err.Source & "-->" & Err.Description, , "Error: " & Err.Number
End If
End Function
If I use "Jet OLEDB:Link Datasource" I get the following errors from ADO:
Error #0
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'mydatabase'.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 01000)
(NativeError: 5701)
Error #0
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 01000)
(NativeError: 5703)
ODBC--connection to '{SQL Server}servername' failed.
-2147467259
If I use "Jet OLEDB:Link Provider String", the code works without stopping but if I try to open a table, query or form that connects to the SQL database, I get a "SQL Server Login" error which indicates failed authentication for a secure connection. The properties that follow here refer to one of the tables that are being re-linked.
LU_APPNAME
Temporary Table False
Jet OLEDB:Table Validation Text
Jet OLEDB:Table Validation Rule
Jet OLEDB:Cache Link Name/Password False
Jet OLEDB:Remote Table Name dbo.LU_APPNAME
Jet OLEDB:Link Provider String DRIVER=SQL Server;SERVER=servername;APP=Microsoft Open Database Connectivity;WSID=A124875;DATABASE=mydatabase
Jet OLEDB:Link Datasource
Jet OLEDB:Exclusive Link False
Jet OLEDB:Create Link True
Jet OLEDB:Table Hidden In Access False
I tried setting
tbl.properties("Jet OLEDB:Cache Link Name/Password") = True in the loop that sets the connection for each table thinking that's what would allow the SQL tables to be accessible through the Access database. That generates run time error -2147217887:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
Suffice it to say I appreciate any input. One of these days. Thanks...
Chas
