Microsoft Office 2000/Visual Basic Programmer's Guide

Opening an Access Database by Using ADO

To open an Access database by using ADO, you use the Connection object. Because you need to use the Jet database engine to open an Access database, you must set the Provider property to specify that ADO use the Microsoft Jet 4.0 OLE DB provider before it opens the Connection object. By default, the Microsoft Jet 4.0 OLE DB Provider opens a connection to a database in shared-access mode (Mode = adModeShareDenyNone). The following procedure establishes a connection to the database specified by the strDBPath argument in shared-access mode.

Sub OpenDBShared (strDBPath As String)
   Dim cnnDB As ADODB.Connection

   ' Initialize Connection object
   Set cnnDB = New ADODB.Connection
   ' Specify Microsoft Jet 4.0 Provider and then open the
   ' database specified in the strDBPath variable.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
      ' Code to work with database goes here.
   End With

   ' Close Connection object and destroy object variable.
   cnnDB.close
   Set cnnDB = Nothing
End Sub

Note   Prior to ADO 2.0 (which was released with Microsoft Visual Studio^® 6.0 and on the Web as part of MDAC 2.0), there was no Microsoft Jet OLE DB Provider. All code written to work against ADO 1.5 used the Microsoft OLE DB Provider for ODBC (which is used by default in ADO) and the Microsoft Access ODBC driver. For this reason, you may see code such as the following code fragment used to open an Access database:

Set cnn = New ADODB.Connection
strCnn = "driver={Microsoft Access Driver (*.mdb)};dbq=c:\Nwind.mdb"
cnn.Open strCnn

Although code that specifies the Microsoft OLE DB Provider for ODBC and the Microsoft Access ODBC driver will continue to work under ADO 2.1, there is no reason to continue to use this method of opening an Access database. The Microsoft Jet 4.0 OLE DB Provider exposes more functionality of the Jet Database engine, and provides better performance than do the Microsoft OLE DB Provider for ODBC and the Microsoft Access ODBC driver.

By default, both DAO and ADO using the Jet OLE DB provider open a database for shared, updatable access. However, there may be times when you want to open the database exclusively or in read-only mode. To do this, you need to explicitly specify the mode used to open the database with the Mode property of the Connection object before you open it. The following code fragment shows how to open a database in shared, read-only mode by using ADO.

Set cnnDB = New ADODB.Connection
With cnnDB
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Mode = adModeRead
   .Open strDBPath
End With

There are a number of Microsoft Jet 4.0 OLE DB Provider-specific properties that you may want or need to set when you open an Access database. For example, if the database is secured by using a database password, you need to use the Jet OLEDB:Database Password property to specify the password used to open the database. The following code fragment shows how to specify a password by using the Properties collection of the Connection object.

cnnDB.Properties("Jet OLEDB:Database Password") = "OpenSesame"

The following table lists a few of the provider-specific initialization properties that you can set by using the Properties collection of the Connection object or the ConnectionString argument of the Open method when you use the Microsoft Jet 4.0 OLE DB Provider.

For a complete list of initialization properties, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM. In addition to initialization properties, the Microsoft Jet provider exposes settings called session properties that can be used to optimize the performance of the Microsoft Jet database engine. For information about session properties, see "Provider-Specific ADO Connection Object Session Properties" in ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.

The following sample procedure specifies the Mode property by using the built-in ADO ConnectModeEnum enumeration, and provides several optional arguments to specify database password, workgroup information file (system database), user ID, user password, and the engine type (which is specified with the user-defined opgJetEngineType enumeration in the JetOLEDBConstants module) when you create a Connection object to open an Access database.

Function GetJetConnection(strPath As String, _
         lngMode As ADODB.ConnectModeEnum, _
         Optional strDBPwd As String, _
         Optional strSysDBPath As String, _
         Optional strUserID As String, _
         Optional strUserPwd As String _
         Optional lngEngineType As opgJetEngineType) _
         As ADODB.Connection
   Dim cnnDB As ADODB.Connection
   
   Set cnnDB = New ADODB.Connection
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Mode = lngMode
      .Properties("Jet OLEDB:Database Password") = strDBPwd
      .Properties("Jet OLEDDB:System Database") = strSysDBPwd
      .Properties("Jet OLEDDB:Engine Type") = lngEngineType
      .Open ConnectionString:=strPath, _
            UserID:=strUserID, _
            Password:=strUserPwd
   End With

   Set GetJetConnection = cnnDB
End Function

The GetJetConnection procedure can be found in the OpenDatabase module of the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.