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.
Printable View
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:
This is just an idea of how to deal with classes and database access ...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