Help. VB Class Modules

[ash10]

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.

[WizBang]

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.

[Patzer]

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:

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 ...