Using DSNLess Connections To MySQL
To create linked Access tables to a remote server, one of the methods commonly used is to create a DSN on every computer that needs to connect. The DSN defines the server's IP Address, port, database name, user and password information and is created through the Administration tools of the Control Panel. This process can be time-consuming.
The DSNLess method uses VBA code and the construction of a connect string to accomplish the same task with no need to create a DSN on each user's PC.
Before you run the code, you need to have all the information mentioned above that would normally be required for a DSN. In addition, on the client computers, you must install the MySQL ODBC connector software and know the version so that it can be specified in your connect string.
Here's an example of connecting to a MySQL database where you know the table names.
Program Code
Option Compare Database
Option Explicit
Public Function ConnectMySQLODBC()
Call AttachDSNLessTable("cst001", "cst001", "192.168.1.113", "spiritleatherworks", "richlocus", "", "3306")
Call AttachDSNLessTable("inv001", "inv001", "192.168.1.113", "spiritleatherworks", "richlocus", "", "3306")
Call AttachDSNLessTable("jnl300", "jnl300", "192.168.1.113", "spiritleatherworks", "richlocus", "", "3306")
Call AttachDSNLessTable("ord320", "ord320", "192.168.1.113", "spiritleatherworks", "richlocus", "", "3306")
Call AttachDSNLessTable("pur001", "pur001", "192.168.1.113", "spiritleatherworks", "richlocus", "", "3306")
End Function
Private Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, _
stServer As String, stDatabase As String, Optional stUserName As String, _
Optional stPassword As String, Optional stPort As String) As Boolean
On Error GoTo AttachDSNLessTAble_Err
Dim td As TableDef
Dim stConnect As String
' **********************************************************
' Delete Old Tables
' **********************************************************
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUserName) = 0 Then
' **********************************************************
' Use Trusted Authentication If stUserName Is Not Supplied
' **********************************************************
stConnect = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=" & stServer _
& ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
stConnect = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=" & stServer _
& ";DATABASE=" & stDatabase & ";UID=" & stUserName & _
";PWD=" & stPassword & ";PORT=" & stPort & ";OPTION=16394"
End If
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