Help. VB Class Modules

Could anyone please help me in using Class modules, sql and database connections? How do I perform a delete and a search fundtion using sql and class moules? Can anyone provide me with a solution or tutorials on this subject? Thanks a million.

Welcome to CodeGuru!

Perhaps the best way to get the answers to your questions is using the search, as there are quite a few threads on such subjects. I'm sure many members can and will offer their help too, and the information you can gather before then will be very useful.

In general I recommend to use 3 Classes.
1) Basic class which properties represent the fields of a table
2) collection class for the abovementioned one
3) Class that handles the database connect and has a method that returns a collection of 2) depending on an optional sql filter string.
You can use the class builder to create 1) and 2)

For 3): A code snippet:

Code:

---

Option Explicit
Option Base 0

Private m_ccol As clsColClass
....

Private Const strRS As String = "SELECT tbl1.* FROM tbl1"


Public Property Set cCol(ByVal vdata As clsColClass)
    Set m_cCol = vdata
End Property

Public Property Get cCol() As clsColclass
    Set cCol = m_cCol
End Property

Public Sub save() ' Just for lexibility, to enable you to do something mor than
                            ' only saving
    saveme
End Sub

Public Sub SetcCol(col As clsColClass)
On Error GoTo ErrHandler

  Set m_cCol = col
 
  saveme

End Sub

Private Sub saveme()

  Dim rscol As ADODB.RecordSet
  Dim i As Integer
 
  '..... ' you now have to set up a recordset for saving  here named rscol
 
  With rsCol
      For i = 1 To m_ccol.Count
        .Filter = "Pkey = " & m_ccol.Item(i).Primarykey ' Filter on Primary key to
                                                                                  ' have unique Record
        If .RecordCount > 1 Then
            MsgBox "Error, Key is not unique!!", vbCritical
        End If
       
        If .RecordCount = 0 Then
            .AddNew
        End If
        !prop1= m_cRout.Item(i).prop1
        '....
        .Update

        m_ccol.item(i).primarykey = !pkey ' return the primary key to the
                                                                  ' class if new record
      Next
      .Close
  End With
 

  Set rsCol = Nothing
  Exit Sub

End Sub

Public Function GetcCol(Optional ByVal strfilter As Variant) As clsColClass

  Dim rstmp As ADODB.RecordSet
 
  Set m_cCol = Nothing
  Set m_cCol = New clsColclass
' Recordset
  If Not IsMissing(strfilter) Then
      Set rstmp = RS(strfilter)
  Else
      Set rstmp = RS
  End If
  ' Daten in die Collection
  With rstmp
      If Not (.EOF And .BOF) Then
        .MoveFirst
      End If
      While Not .EOF
        m_cRout.Add !pkey, !prop1 '....
        .MoveNext
      Wend
      .Close
  End With
  Set GetcCol = m_ccol
 
 

    Set rstmp = Nothing

End Function

Private Function RS(Optional ByVal strsql As Variant) As ADODB.RecordSet
 
  Dim rstmp As ADODB.RecordSet
 
' Recordset handling ...
 
  Set RS = rstmp
 
  Set rstmp = Nothing
 
End Function

Private Sub Class_Initialize()
  Set m_cCol = New clsColClass
End Sub

Private Sub Class_Terminate()
  Set m_cCol = Nothing
End Sub

---

This is just an idea of how to deal with classes and database access ...