Hi SJ,

Glasgow is very bright today eh?

SJ, I was wondering if you could point me in the right direction. I have been using MS Access and Visual Basic for access for the past two years and have managed via the help of this forum to create cool functional databases.
However, I have recently installed MySQL 4.0.25 and Visual Basic 6.0 Professional and I would like to be able to switch away from access.

I have installed MySQL in my C Drive and I access mysql by typing: mysql -u root mysql. I am then presented with the prompt for SQL and I have been able to create databases on my c drive. I would like to create an application in Visual Basic that uses a MYSQL database for data and I am totally stuck at that point.
I am having great difficulty understanding and configuring ODBC and I have also read that databases can connect via ADO in VB6. I am lost lol.

Do you know if there is any step-by-step instructions as I feel a total idiot.

Kind Regards

I've not done it myself but you'll find the connection information you need at ConnectionStrings.com (http://www.connectionstrings.com/).

Yes, databases can be connected to using ADO.

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

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

Thanx SJ,

I will try that when I go home. Do I just include that code in the VB form?

You could so.

Personally, I'd be inclined to make a Class to deal with connections.

Now you've totally lost me lol.

Thanks I've been trying to workout a sulution for this for weeks.

mick