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

Class to support client/server operations using the ADO feature in VBA and VB6.

This class sets up an ADO connection object for use with a Microsoft Jet database. It uses the Jet ODBC driver rather than the Jet OLE DB driver.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CADOConnJetODBC class.
Attributes Property Get the Connection attribute flags used to open the connection to the database. For a Connection object, the Attributes property is read/write, and its value can be the sum of any one or more of the XactAttributeEnum values (default is zero).
CommandTimeout Property Get the value of the CommandTimeout property of the connection. Use this property to allow the cancellation of an Execute method call, due to delays from network traffic or heavy server use.
Connection Property Get a pointer to the local Connection object that was created by this class. This property is read-only. You may use this property to gain access to all of the properties and methods of the ADODB Connection object via an object variable declared from the CADOConnJetODBC class.
ConnectionTimeout Property Get the value of the ConnectionTimeout property of the connection. Use this property on a Connection object if delays from network traffic or heavy server use make it necessary to abandon a connection attempt.
ConnectString Property Get the value of the ConnectString property of the connection. This property is a combination of the values supplied as properties of this class and additional values supplied after the connection is made.
CursorLocation Property Get the value of the CursorLocation property of the connection. This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.
DataSource Property Get the name of the server machine used to create the connection. The name may use either mapped drive letters or follow the UNC standard for referring to server and share locations.
DefaultDir Property Get the default directory used to create the connection.
Driver Property Get the ODBC driver used to create the connection. Normally this property should not be changed from its default value of "{Microsoft Access Driver (*.mdb)}".
IsolationLevel Property Get the value of the IsolationLevel property of the connection. Use this property to set the isolation level of a Connection object. The IsolationLevel property is read/write. The setting does not take effect until the next time you call the BeginTrans method. If the level of isolation you request is unavailable, the provider may return the next greater level of isolation.
Mode Property Get the value of the Mode property of the connection. Use this property to set or return the access permissions in use by the provider on the current connection. You can set the Mode property only when the Connection object is closed.
Password Property Get the password used to log into the database. If the Jet database is secured with Jet security, use this property to specify the password for the user that is logging into the database. This property is used in conjunction with the UserID and SystemDB properties.
SystemDBName Property Get the fully-qualified path to the Microsoft Jet workgroup security database. If the Jet database is secured with Jet security, use this property to specify the location of the workgroup system information database. This property is used in conjunction with the UserID and Password properties.
UserID Property Get the user name used to log into the server. If the Jet database is secured with Jet security, use this property to specify the name of the user to log into the database. This property is used in conjunction with the Password and SystemDB properties.
Class_Initialize Initialize Set initial values to defaults which may be overridden with property settings.
Class_Terminate Terminate Release resources used by the class.
CloseConnection Method Close the current connection to the ODBC database to free any associated system resources.
OpenConnection Method Open the ADODB Connection to the specified ODBC database, using the properties of this class to control parameters of the connection.
BuildConnectString Private Create the string used as the ConnectString property of the class, which is the value used to open the connection.
m_Connection_BeginTransComplete Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the BeginTransComplete events on to the user of this class.
m_Connection_CommitTransComplete Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the CommitTransComplete events on to the user of this class.
m_Connection_ConnectComplete Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the ConnectComplete events on to the user of this class.
m_Connection_Disconnect Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the Disconnect events on to the user of this class.
m_Connection_ExecuteComplete Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the ExecuteComplete events on to the user of this class.
m_Connection_InfoMessage Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the InfoMessage events on to the user of this class.
m_Connection_RollbackTransComplete Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the RollbackTransComplete events on to the user of this class.
m_Connection_WillConnect Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the WillConnect events on to the user of this class.
m_Connection_WillExecute Private The private local ADODB.Connection variable raises connection related events. This procedure simply passes the WillExecute events on to the user of this class.
' 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

Total Visual SourceBook 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

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS