Class: ADOConnJetODBC in Category Access/Jet Databases : Database from Total Visual SourceBook

' Example of CADOConnJetODBC
'
' To try this example, do the following:
' 1. Create a new form
' 2. Create a command button 'cmdTest'
' 3. Paste all the code from this example to the new form's module.

' This example assumes that the sample files are located in the folder named by the following constant.

Private Const mcstrSamplePath As String = "C:\TVSBSamp"
Private WithEvents mADOConnJetODBC As CADOConnJetODBC

Private Sub cmdTest_Click()
  ' Comments: This example creates the CADOConnJetODBC object. The connection is made asynchronously.
  '           When the connection is complete, the ConnectComplete event is used to open a recordset using the connection.

  Set mADOConnJetODBC = New CADOConnJetODBC

  With mADOConnJetODBC
    .UserID = "Admin"
    .Password = ""
    .CursorLocation = adUseServer
    .DataSource = mcstrSamplePath & "\sample.mdb"
    .Mode = adModeRead

    ' When this command is executed, the mADOConnJetODBC_ConnectComplete procedure is invoked since the called class raises the ConnectComplete event.
    .OpenConnection
  End With

End Sub

Private Sub Form_Load()
  cmdTest.Caption = "Test"
End Sub

Private Sub mADOConnJetODBC_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
  ' Comments: Event procedure that is raised by the CADOConnJetODBC when the connection is completed.
  '           Perform tasks, update the user interface, etc. when the connection is completed

  Dim rstProducts As ADODB.Recordset
  Dim strSQL As String

  strSQL = "SELECT ProductName FROM Products ORDER BY ProductName"

  If pError Is Nothing Then
    If pConnection.state = adStateOpen Then
      Debug.Print "Connect string: " & pConnection.ConnectionString

      ' In this example, show the list of products in the Immediate Window
      Set rstProducts = pConnection.Execute(strSQL)
      If Not rstProducts.EOF Then
        Do Until rstProducts.EOF
          Debug.Print rstProducts("ProductName")
          rstProducts.MoveNext
        Loop
      End If
      rstProducts.Close
      Set rstProducts = Nothing

      MsgBox "Connection successfully opened"
    Else
      MsgBox "Unable to open the connection"
    End If
  Else
    MsgBox "Unable to open the connection"
  End If

End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)