Opening a Database

Generally, one of the first steps in writing an application to access data is to open the data source. When using the Microsoft Jet database engine, you can open Microsoft Jet databases, other external data sources such as Microsoft Excel, Paradox, and dBASE with Microsoft Jet's ISAM components, and ODBC data sources.

Microsoft Jet Databases

The Microsoft Jet Provider can open Microsoft Jet 4.0 databases as well as databases created with previous versions of the Microsoft Jet database engine. These examples use only Microsoft Jet 4.0 databases.

The following code demonstrates how to open a Microsoft Jet database for shared, updatable access. Then the code immediately closes the database because this code is for demonstration purposes.

DAO

Sub DAOOpenJetDatabase()

   Dim db As DAO.Database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
   db.Close

End Sub

ADO

Sub ADOOpenJetDatabase()

   Dim cnn As New ADODB.Connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"
   cnn.Close

End Sub

These two code listings for opening a database look somewhat different, but are not all that dissimilar. Aside from the fact that the objects have different names, the major difference is the format of the string passed to the method that opens the database.

The ADO connection string in this example has two parts: the provider tag and the data source tag. The provider tag indicates which OLE DB Provider to use, and the data source tag indicates which database to open. With DAO it is assumed that you want to use Microsoft Jet, whereas with ADO you must explicitly specify that you want to use Microsoft Jet.

By default, both DAO and ADO open a database for shared updatable access, when using the Microsoft Jet Provider. However, there may be times when you want to open the database exclusively or in read-only mode.

The following code listings show how to open (and then close) a shared, read-only database using DAO and ADO.

DAO

Sub DAOOpenJetDatabaseReadOnly()

   Dim db As DAO.Database

   ' Open shared, read-only.
   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", False, True)
   db.Close

End Sub

ADO

Sub ADOOpenJetDatabaseReadOnly()

   Dim cnn As New ADODB.Connection

   ' Open shared, read-only
   cnn.Mode = adModeRead
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"
   cnn.Close

End Sub

In the DAO listing the second two parameters to the OpenDatabase method indicate exclusive and read-only access respectively. In the ADO listing the Connection object's Mode property is set to the read-only constant (adModeRead). By default, ADO connections are opened for shared, updatable access unless another mode is set (for example, adModeShareExclusive).

Alternatively, the ADO listing could have been written in a single line of code as follows:

Sub ADOOpenJetDatabaseExclusive()

   Dim cnn As New ADODB.Connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;Mode=" & adModeRead
   cnn.Close

End Sub

In this listing the Mode property was specified as a part of the connection string to the Open method rather than as a property of the Connection object. In ADO you can set connection properties as a property or string them together with other properties to create the connection string. Even provider-specific properties (prefixed by "Jet OLEDB:" for Microsoft Jet–specific properties) can be set as part of the connection string or with the Connection object's Properties collection. For a description of the available properties, see "Appendix B: Microsoft Jet 4.0 OLE DB Properties Reference" later in this article.

Setting Microsoft Jet Options

The Microsoft Jet database engine exposes a number of settable options that will dictate how the engine will behave. These options often have a direct impact on performance. By default when the Microsoft Jet database engine is initialized, it uses the values set in the Windows registry under the \HKEY_LOCAL_MACHINES\Software\Microsoft\Jet key. At run time, it is possible to temporarily override these settings. In ADO these values are set as part of the connection string.

The following listings demonstrate how to override the Page Timeout setting of the engine and open a database using that setting.

DAO

Sub DAOSetJetDBOption()

   Dim db As DAO.Database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
   DBEngine.SetOption dbPageTimeout, 4000
   db.Close

End Sub

ADO

Sub ADOSetJetDBOption()

   Dim cnn As New ADODB.Connection

   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
   cnn.Open ".\NorthWind.mdb"
   cnn.Properties("Jet OLEDB:Page Timeout") = 4000
   cnn.Close

End Sub

With DAO you use the SetOption method to set the values for these database settings. There is no corresponding GetOption method to retrieve the values. With ADO you use a property in the Connection object's Properties collection. You can read the value of the property using ADO; however, this value is not accurate unless you have previously set the value for the property. For example, the Jet OLEDB:Page Timeout property will return the value 0 prior to setting this property even though the value defined for this property in the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0\PageTimeout registry key is actually 5000.

Another minor difference between ADO and DAO is that with ADO the Connection must be opened before these properties are available. With DAO these properties can be set on the DBEngine object prior to opening the database.

As shown in the listings, you can optionally set the provider in the Provider property, rather than in the connection string. The "Data Source=" section of the connection string can also be omitted, and ADO will assume this is the default value for the path in the connection string. This is simply an alternative method of opening a connection; with ADO there are sometimes many equally valid ways to accomplish a task. Later in this article, the section "Opening a Database with User-Level Security" explains a scenario in which it is required that you indicate the provider in the Provider property rather than in the connection string.

The following table lists the values that can be set with DAO's SetOption method and the corresponding property to use with ADO.

Secured Microsoft Jet Databases

Microsoft Jet databases can be secured in one of two ways: with either share-level security or user-level security. With share-level security, the database is secured with a password. Anyone attempting to open the database must specify the correct database password. With user-level security, each user is assigned a user name and password to open the database. Microsoft Jet uses a separate workgroup information file, typically named "system.mdw" to store user information and passwords. See the section "Security" for more information about creating and using secured Microsoft Jet databases.

Share-Level (Password Protected) Databases

The following listings demonstrate how to open a Microsoft Jet database that has been secured at the share level.

DAO

Sub DAOOpenDBPasswordDatabase()

   Dim db As DAO.Database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", _
      False, False, ";pwd=password")
   db.Close

End Sub

ADO

Sub ADOOpenDBPasswordDatabase()

   Dim cnn As New ADODB.Connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;" & _
      "Jet OLEDB:Database Password=password;"
   cnn.Close

End Sub

In DAO the Connect parameter of the OpenDatabase method sets the database password when opening a database. With ADO the Microsoft Jet Provider connection property Jet OLEDB:Database Password sets the password instead.

Opening a Database with User-Level Security

These next listings demonstrate how to open a database that is secured at the user level using a workgroup information file named "system.mdw."

DAO

Sub DAOOpenSecuredDatabase()

   Dim wks As DAO.Workspace
   Dim db As DAO.Database

   DBEngine.SystemDB = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   Set wks = DBEngine.CreateWorkspace("", "Admin", "")
   Set db = wks.OpenDatabase(".\NorthWind.mdb")

   db.Close
   wks.Close

End Sub

ADO

Sub ADOOpenSecuredDatabase()

   Dim cnn As New ADODB.Connection

   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
   cnn.Properties("Jet OLEDB:System database") = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"
   cnn.Close

End Sub

In ADO a Microsoft Jet Provider-specific connection property, Jet OLEDB:System database, specifies the system database. This is equivalent to setting the DBEngine object's SystemDB property before opening a database using DAO.

Notice that, in this example, the Provider property is set as a property of the Connection object rather than as part of the ConnectionString argument to the Open method. That is because before you can reference provider-specific properties from the Connection object's Properties collection, it is necessary to indicate which provider you are using. If the first line of code had been omitted, error 3265 (adErrItemNotFound), "ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application," would have occurred when trying to set the value for the Jet OLEDB:System database property.

Note that in both DAO and ADO setting the system database may not be necessary. You may omit the code that sets the system database if you want to use the current Microsoft Jet workgroup information file as specified in the SystemDB key in the Microsoft Jet registry entries. See the book Microsoft Jet Database Engine Programmer's Guide (available from Microsoft Press Online at http://mspress.microsoft.com/) for more information about Microsoft Jet Security.

External Databases

The Microsoft Jet database engine can be used to access other database files, spreadsheets, and textual data stored in tabular format through installable ISAM drivers.

The following listings demonstrate how to open a Microsoft Excel 2000 spreadsheet first using DAO, and then using ADO and the Microsoft Jet Provider.

DAO

Sub DAOOpenISAMDatabase()

   Dim db As DAO.Database

   Set db = DBEngine.OpenDatabase(".\Sales.xls", _
      False, False, "Excel 8.0;")

   db.Close

End Sub

ADO

Sub ADOOpenISAMDatabase()

   Dim cnn As New ADODB.Connection

   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\Sales.xls" & _
      ";Extended Properties=Excel 8.0;"

   cnn.Close

End Sub

The DAO and ADO code for opening an external database is similar. In both examples, the name of the external file (Sales.xls) is used in place of a Microsoft Jet database file name. With both DAO and ADO you must also specify the type of external database you are opening—in this case, an Excel 2000 spreadsheet. With DAO the database type is specified in the Connect argument of the OpenDatabase method. The database type is specified in the Extended Properties property of the Connection with ADO. The following table lists the strings to use to specify which ISAM to open.

Note that if you are migrating from DAO 3.5 or earlier with the FoxPro ISAM to ADO with the Microsoft Jet Provider, you will need to use Microsoft Visual FoxPro® ODBC Driver because Microsoft Jet 4.0 does not support the FoxPro ISAM.

The Current Microsoft Access Database

When you open Microsoft Access, you are opening a Microsoft Jet database. When writing code within Access, you may often want to use the same connection to Microsoft Jet as Access is using. To allow you to do this, Microsoft Access 2000 exposes two mechanisms: CurrentDB() and CurrentProject.Connection allow you to get a DAO Database object and an ADO Connection object, respectively, for the database Access currently has open.

The following listings demonstrate how to get a reference to the database currently open in Microsoft Access.

DAO

Sub DAOGetCurrentDatabase()

   Dim db As DAO.Database

   Set db = CurrentDb()

End Sub

ADO

Sub ADOGetCurrentDatabase()

   Dim cnn As ADODB.Connection

   Set cnn = CurrentProject.Connection

End Sub