Connect to mySQL Database with Visual Basic

Mile-O said:

I've not done it myself but you'll find the connection information you need at ConnectionStrings.com.

Yes, databases can be connected to using ADO.

I found this code, by Mark Gambo, on vbForums:

Code:

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    
else
    For b = 1 To rs.RecordCount
    '<do whatever you need to do with the data here>
    Next b
end if
 
ExitSub:
 
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 
On Error GoTo 0
Exit Sub
 
Command1_Click_Error:
    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  & _
                ";OPTION=3;"
 
End Function

Click to expand...

For b = 1 To rs.RecordCount

For b = 0 To rs.RecordCount -1