I'm a bit new to VB (actually use VBA), but would like to get some knowlegde about class module. One thing I was thinking of would be to create a class module which take cares of the connection to the MYSQL database.
Please let me know if you have some bit of code to start me off.
Thank you

NB: I use ADO 2.8 or lower

I use the following code in Access to connect to a mysql database using ADO 2.8 and a system DNS:
If anybody have ideas to improve it, please let me know. For instance, I'm not sure how to handle the error event withing the class module!

in a class module named clsMYSQL, I have the follwoing code:

Option Compare Database
Option Explicit

Public Cnxn As ADODB.Connection
Public cmdChange As ADODB.Command
Public rstList As ADODB.Recordset
Private strCnxn As String
Private myAddUseServer As Boolean 'default=false
Public Property Let IsCursorLocationAddUseServer(Value As Boolean)

myAddUseServer = Value
End Property
Public Property Get IsCursorLocationAddUseServer() As Boolean
IsCursorLocationAddUseServer = myAddUseServer
End Property
Private Sub Class_Initialize()

'define path to connection
strCnxn = "DSN=MySQLMasterAtWork"
'opens connection password and user name already define
Set Cnxn = New ADODB.Connection

If myAddUseServer = False Then
Cnxn.CursorLocation = adUseClient
Cnxn.CursorLocation = adUseServer
End If
Cnxn.Open strCnxn
End Sub

Private Sub Class_Terminate()

Set Cnxn = Nothing
End Sub

Sub MySQL_UPDATE(mySQLcmd As String)
'will work for SQL request which demands no feedback, ie UPDATE and INSERT and DELETE Requests
Cnxn.Execute mySQLcmd, , adExecuteNoRecords
End Sub

Function MySQL_SELECT(mySQLcmd As String) As Boolean
'Dim mySQL As clsMYSQL use that thread in thec cfode
'Set mySQL = New clsMYSQL

Set cmdChange = New ADODB.Command

With cmdChange
.ActiveConnection = Cnxn
.CommandText = mySQLcmd
End With

Set rstList = New ADODB.Recordset
If Cnxn.CursorLocation = adUseClient Then
rstList.CursorType = adOpenStatic
rstList.CursorType = adOpenDynamic
End If

Set rstList = cmdChange.Execute

If rstList.EOF = True And rstList.BOF = True Then 'SEND VALUE in form
'Note: The BOF and EOF properties are set to True if you open an empty Recordset.RecordCount property is zero.

Set cmdChange = Nothing
'Set rstList = Nothing
End If
End Function

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.