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

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

This class supports client/server operations using Microsoft's ActiveX Data Objects (ADO) technology. When using Visual Basic to deploy data-oriented local or client/server applications, you have a variety of development choices. You can use DAO and Jet, DAO and ODBC Direct, RDO and the Remote Data Control, or program directly against the ODBC API. This class exposes functionality to use ADO against Microsoft Jet data sources using the native Jet OLE DB driver. For information on the other choices, see the other classes in the Database category.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CADOConnJet 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 CADOConnJet 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.
DatabasePassword Property Get the text of database-level password used to create the connection to the Jet database. Note that the DatabasePassword property is separate from the user-level password needed when using Jet security.
DataSource Property Get the string containing the path to the Jet database 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.
IsolationLevel Property Get the value of the IsolationLevel property of the connection. This 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.
JetVersion Property Get the version number of the Jet OLE DB driver used to create the connection. Valid settings are: - acjVersion351 = Jet 3.51 - acjVersion400 = Jet 4.0 - acjVersion12 = Jet 12.
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 text of the password used to log into the database. This property is used in conjunction with the UserID and SystemDB properties.
Provider Property Get the string containing the ODBC provider name used to create the connection. This property is read/write when the connection is closed and read-only when it is open. The setting does not take effect until you either open the Connection object or access the Properties collection of the Connection object. Normally this value will not need to be changed for an OLE DB Jet connection. It is automatically set.
RegistryPath Property Get the text of the registry key location where Jet initialization information is stored. You may set up an entry in the system registry that controls various parameters of the Jet database engine. This property indicates the location in the registry to find those settings. See the Microsoft Jet documentation for information on the values associated with the registry entry.
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 string containing 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.
OpenConnection Method Open the ADODB Connection to the specified database, using the properties of this class to control parameters of the connection. If this method completes, the connection is live and you can issue commands against it and process the results.
OpenDatabase Method Open an ADODB Connection to the specified database without setting all the options (useful for non-password protected databases) Make sure JetVersion property is already set.
BuildConnectString Private Builds up the string used as the ConnectString property of the class, which is the value used to open the connection. The various property settings of this class are constructed into the actual ConnectString which are used to create the connection. This helper procedure handles creating the string.
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.
ADOUserCount Method Count the number of users (actually current connections) to the database. This includes at least one for the passed connection. When it is one, it means no other users are in the database.
ADOUserComputersToString Method Provide the list of connected computers in a delimited string.
ADOUserRosterToString Method Uses the Jet User Roster to list all users in the specified database Note that a database opened on a read-only share cannot create a lockfile. The user roster against such a file does not work.
ADOPassiveShutdown Method Uses the Jet passive shutdown technology to prevent/allow users from issuing any new attempts to open the named database.
TrimNull Private Get the passed string terminated at the first null character. If there isn't a null, the entire string is returned.
' Example of CADOConnJet
'
' To try this example, do the following:
' 1. Create a new user form in your project.
' 2. Create the following command buttons
'       cmdOpenConn
'       cmdUsers
' 3. Paste this code into the form's module

Private Const mcstrSampleDatabase As String = "C:\Total Visual SourceBook 2013\Samples\Sample.mdb"
Private Const mcstrTable As String = "Categories"
Private WithEvents mclsADOConnJet As CADOConnJet

#If VBA7 Then
  Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#Else
  Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#End If

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

  Dim strError As String

  Debug.Print "Connecting..."

  Set mclsADOConnJet = New CADOConnJet

  With mclsADOConnJet
    ' Use ACE provider for Access 2007 or later to support ACCDB and MDB databases
    .JetVersion = acjVersion12

    .UserID = "Admin"
    .Password = ""
    .CursorLocation = adUseServer
    .DataSource = mcstrSampleDatabase
    .Mode = adModeRead
    strError = .OpenConnection
  End With

  If strError = "" Then
    MsgBox "Database successfully opened"
  Else
    MsgBox strError
  End If

End Sub

Private Sub cmdUsers_Click()
  ' Comments: Display the current users in the database

  Dim clsJetConnection As New CADOConnJet
  Dim strError As String
  Dim intUsers As Integer
  Dim strUserInfo As String

  MsgBox "Before running this routine, open the database: " & vbCrLf & mcstrSampleDatabase & vbCrLf & " in another instance of Access to simulate another user."

  With clsJetConnection
    ' Set the database version to ACE provider for Access 2007 or later to support ACCDB and MDB databases
    .JetVersion = acjVersion12

    strError = .OpenDatabase(mcstrSampleDatabase)
    If strError = "" Then
      ' See how many connections there are to the database
      intUsers = .ADOUserCount()

      strUserInfo = "Current user count: " & intUsers & vbCrLf & _
                    "List of users: " & .ADOUserComputersToString() & vbCrLf & _
                    "List of user details: " & .ADOUserRosterToString()
      MsgBox strUserInfo

      If intUsers > 1 Then
        If .ADOPassiveShutdown(True) Then
          ' For it to remain active, the connection variable cannot be closed
          ' Here, we keep it open until there's only one connection left
          MsgBox "This code waits until all other copies of the database are closed."

          Do Until intUsers = 1
            intUsers = .ADOUserCount()

            ' Pause a second and check again
            WaitSeconds 1
          Loop

          MsgBox "All other users have exited the database"
        End If
      Else
        MsgBox "No other users are in the database"
      End If
    Else
      MsgBox strError
    End If

    .CloseConnection

  End With

  Set clsJetConnection = Nothing
End Sub

Private Sub Form_Load()
  Me.cmdOpenConn.Caption = "Test OpenConnection"
  Me.cmdUsers.Caption = "Test MultiUser"
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set mclsADOConnJet = Nothing
End Sub

Private Sub mclsADOConnJet_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
  ' Comments: The procedure which is called when the class raises the ConnectComplete event. This is where you can update the user interface.

  Dim rst As ADODB.Recordset
  Dim strSQL As String

  strSQL = "SELECT * FROM " & mcstrTable

  If pError Is Nothing Then
    If pConnection.state = adStateOpen Then
      Debug.Print "Connect String: " & pConnection.ConnectionString
      Set rst = pConnection.Execute(strSQL)
      If Not rst.EOF Then
        Do Until rst.EOF
          Debug.Print rst(1)
          rst.MoveNext
        Loop
      End If
    Else
      Debug.Print "Connection failure, invalid state"
      MsgBox "Unable to open the connection"
    End If
  Else
    Debug.Print "Connection failure"
    MsgBox "Unable to open the connection"
  End If

End Sub

' Dependencies: (Declarations)
Private Sub WaitSeconds(intSeconds As Integer)
  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds    Number of seconds to wait
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Dim datTime As Date

  datTime = DateAdd("s", intSeconds, Now)

  Do
    ' Yield to other programs (better than using DoEvents alone which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datTime

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
  Resume PROC_EXIT
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