VBA (Visual Basic) Coding for Databases

Posting of Database Coding Review
Set connection to Back end database:


Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & CurrentProject.Path & "\NameofDatabase.accdb;"

Write the Select Statement to get data:

Dim rsName As ADODB.Recordset ' put this at top

Set rsName = New ADODB.Recordset

With rsName

    Set .ActiveConnection = cn

    .Source = "Select * From TableName"

    .LockType = adLockOptimistic

    .CursorType = adOpenStatic

    .Open

End With

Fill text boxes on Form with data retrieved:

With rsName

    If Not (.EOF) Then

        .Find strcriteria

        Me.txtTransactionID = !transactionid

        Me.txtDateAcquired = !DateAcquired

        Me.txtAcquisitionPrice = !AcquisitionPrice

                

        Me.txtAskingPrice = !AskingPrice

        Me.txtDateSold = !datesold

        Me.txtSalesPrice = !salesprice

    End If

End With

Example of Form Load where lsTransaction (Listbox) is filled with transaction ID, Date Acquired and artwork ID:

Dim rsTransactions As ADODB.Recordset

Private Sub Form_Load()

Dim cn As New ADODB.Connection

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & CurrentProject.Path & "\VRG+Art+BE.accdb;"

Set rsTransactions = New ADODB.Recordset

With rsTransactions

    Set .ActiveConnection = cn

    .Source = "Select * from GalleryTransactions"

    .LockType = adLockOptimistic

    .CursorType = adOpenStatic

    .Open

End With

Do While (Not rsTransactions.EOF)

Me.lsTransactions.AddItem rsTransactions![transactionid] & ", " & rsTransactions![DateAcquired] & ", " & rsTransactions![artworkid]

rsTransactions.MoveNext

Loop

rsTransactions.MoveFirst

Set cn = Nothing

End Sub