Hello everyone,
While I was reading Access Cookbook by Ken Getz, Paul Litwin & Andy Baron found a code that allows user at log-in time to Dynamically Link SQL Server Tables at Runtime. I modified the code hoping it will work for MySQL too. I am getting the following error:
3170: Couldn’t find installable ISAM.
Private Sub cmdConnect_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strconnect As String
Dim strMsg As String
Dim userName As String
Dim passWord As String
userName = Me!txtUser
passWord = Me!txtPwd
On Error GoTo HandleErr
' Build base authentication strings
Call MySQL_Connection(userName, passWord)
strconnect = strconnectMySQL
' Get rid of old links, if any
'Call DeleteLinks
' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If
' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strconnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
tdf.RefreshLink
rst.MoveNext
Loop
strMsg = "Tables linked successfully."
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
ExitHere:
MsgBox strMsg, , "Link SQL Tables"
Exit Sub
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select
End Sub
Your help if very much appreciated.
Maru
While I was reading Access Cookbook by Ken Getz, Paul Litwin & Andy Baron found a code that allows user at log-in time to Dynamically Link SQL Server Tables at Runtime. I modified the code hoping it will work for MySQL too. I am getting the following error:
3170: Couldn’t find installable ISAM.
Private Sub cmdConnect_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strconnect As String
Dim strMsg As String
Dim userName As String
Dim passWord As String
userName = Me!txtUser
passWord = Me!txtPwd
On Error GoTo HandleErr
' Build base authentication strings
Call MySQL_Connection(userName, passWord)
strconnect = strconnectMySQL
' Get rid of old links, if any
'Call DeleteLinks
' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If
' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strconnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
tdf.RefreshLink
rst.MoveNext
Loop
strMsg = "Tables linked successfully."
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
ExitHere:
MsgBox strMsg, , "Link SQL Tables"
Exit Sub
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select
End Sub
Your help if very much appreciated.
Maru