Anyone got any ideas on how to re-attach SQL Server linked tables please?
I need to re-create a connection with a different user after clicking a LOGIN button.
I have been running around in circles now trying to figure it out and am at the point where I need to ask for help.
I have tried the following:
---
Public Function SQLServerLinkedTableRefresh(CurrentUser, UserPwd)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim sConnString As String
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={DBSrv01};" & _
"Database=DebtMan;" & _
"Uid=" & CurrentUser & ";" & _
"Pwd=& UserPwd & ;"
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "PASS-THROUGH" And tbl.Name = "tbl_Person" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString ' Error here: "Cannot find field 'SID'"
End If
Next
End Function
I get an error: "Cannot find field 'SID'" at the point displayed. I seem to be chasing my tail a bit here.
Sean
I need to re-create a connection with a different user after clicking a LOGIN button.
I have been running around in circles now trying to figure it out and am at the point where I need to ask for help.
I have tried the following:
---
Public Function SQLServerLinkedTableRefresh(CurrentUser, UserPwd)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim sConnString As String
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={DBSrv01};" & _
"Database=DebtMan;" & _
"Uid=" & CurrentUser & ";" & _
"Pwd=& UserPwd & ;"
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "PASS-THROUGH" And tbl.Name = "tbl_Person" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString ' Error here: "Cannot find field 'SID'"
End If
Next
End Function
I get an error: "Cannot find field 'SID'" at the point displayed. I seem to be chasing my tail a bit here.
Sean