Hi,
I have got a database with linked tables to SQL Server. I would like to use below string to connect to my tables without DSN. I know the string works as if I make a test using the ADODB.Recordset, it connects and returns the result of my test query.
But I use it to connect to tables, I get the "Could not find installable ISAM" error. Can anybody help?
String
Procedure
Cross:
http://www.utteraccess.com/forum/Find-Installable-Isam-t2019938.html
I have got a database with linked tables to SQL Server. I would like to use below string to connect to my tables without DSN. I know the string works as if I make a test using the ADODB.Recordset, it connects and returns the result of my test query.
But I use it to connect to tables, I get the "Could not find installable ISAM" error. Can anybody help?
String
Code:
"Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=%UserID%;password=%PassWord%;Initial Catalog='%DataBase%';Data Source=%Source%;" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Connect Timeout=100000;"
Procedure
Code:
Function testconncetion()
Call AttachDSNLessTable("dbo_TB Company", "dbo.TB Company")
End Function
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
stConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=%User ID%;password=%PassWord%;Initial Catalog=%DataBase%;Data Source=%Source%;" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Connect Timeout=100000;"
Debug.Print stConnect
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
Cross:
http://www.utteraccess.com/forum/Find-Installable-Isam-t2019938.html
Last edited: