Access  - query MySQL database online

Murray Brown
Murray Brown used Ask the Experts™

What Access VBA code would I use to query an online MySQL database, if this is possible.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
You do it the same way I described in your other question:

Except you use a connection string that works with the MySQL Driver that you're using. If you're using the MySQL Connector/ODBC 5.1 driver on your local machine, you'd do this:

con.Open "Driver={MySQL ODBC 5.1 Driver};Server=YourServerIPAddress;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;"

Connection string from here:


Dim strDataBaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Private Sub Command1_Click()
On Error GoTo Command1_Click_Error
Dim b as Long
strDBCursorType = adOpenDynamic  'CursorType
strDBLockType = adLockOptimistic   'LockType
strDBOptions = adCmdText         'Options

Set cn = New ADODB.Connection
Me.MousePointer = 11

cn.Open ConnectString()
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With

    Set rs = New ADODB.Recordset       'Creates record set

    strSQL = "<Your SQL Here>"
    rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions

if rs.Eof then
   Goto ExitSub    
    For b = 1 To rs.RecordCount
	'<do whatever you need to do with the data here>
    Next b
end if

Set rs = Nothing
Set cn = Nothing

On Error GoTo 0
Exit Sub

    MsgBox "Error " & Err.Number & " (" & Err.Description & ")  & _
                     in procedure Command1_Click of Form " & Me.Name
End Sub

Private Function ConnectString() As String
Dim strServerName as String
Dim strDatabaseName as string
Dim strUserName as string
Dim strPassword as string

    'Change to IP Address if not on local machine
    'Make sure that you give permission to log into the
    'server from this address
    'See Adding New User Accounts to MySQL
    'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver 

strServerName = "localhost" 
strDatabaseName = "DatabaseName"
strUserName = "UserName"
strPassword ="Password"

ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword  & _

End Function

Open in new window

yes you can use Driver={MySQL ODBC 5.1 Driver};
Murray Developer



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial