mySQL: How to implement/use?

[wpearsall]

OK This is how i am doing mine,
Its working great!

Declarations:

Const mySqlHost = "localhost"
Const DataBase = "myDataBase"
Const MySqlUID = "root"
Const MySqlUID = ""

Usage:

On Error GoTo Error_Msg:
Dim oConn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
 
' Creating the Connection Socket
oConn.Open "Driver={mySQL};" & _
           "Server=" & mySqlHost & ";" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=" & DataBase & ";" & _
           "Uid=" & MySqlUID & ";" & _
           "Pwd=" & MySqlPass & ";"
 
' SQL Query to preform,
' Remember when using WHERE stuff, you need to use ' around stuff like "User" etc.
mySQL = "SELECT uid, uname, pass from " & UserTable & " WHERE uname='" & User & "'"
 
' Just set the cursor location etc for the record set
myrs.CursorLocation = adUseClient
' Create the record set By executing the MySQL query (Dont need a record set for Update / Delete / Insert :)
Set myrs = oConn.Execute(mySQL)
 
' Now, There may be an error if no records are found, if checking The Password agains the password in the Database (myrs("pass"))
On Error GoTo CheckError:
 
    If (myrs("pass") = Password) Then
              ' Do my authorize access here
    Else
              ' Do my unauthorized access here
    End If
 
' We use a "GoTo" for the error, so i use this for it :)
GoTo CloseUp:
 
Exit Sub
 
CloseUp:
      ' Close the connection
      oConn.close
      ' Created a Object to connect, so lets delete it
      set oConn = Nothing
      ' As well as delete the record set
      set myrs = Nothing
Exit Sub
 
CheckError:
    If Err.Number = 3021 Then ' 3021 = No Records Found
        ' Since no records are found, there isnt a username like dat,
        ' Do failed Login
        GoTo CloseUp:
    Else
        MsgBox Err.Description
        Err.Clear
        Resume Next
    End If
Exit Sub

REMEMBER: ALWAY DELETE MADE CONTROLS
ALWAYS CLOSE CONNECTIONS!

Insert use like:

SQL = "INSERT INTO " & Table & " (field1, field2, field3) VALUES ('Field1Val', 'Field2Val', 'Field3Val')"

To do a "While(List(...)...){":

Do until mrrs.EOF
      ' Use it however you need to here
      txtLog.SelText = myrs("uname") & vbcrlf
      ' Move to the next record
      myrs.MoveNext
Loop

Hope this helps