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, updateable 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 updateable 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, updateable 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: Properties Reference" later in this
document.
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 document, 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.
DAO constant
|
ADO property
|
dbPageTimeout
|
Jet OLEDB:Page Timeout
|
dbSharedAsyncDelay
|
Jet OLEDB:Shared Async Delay
|
dbExclusiveAsyncDelay
|
Jet OLEDB:Exclusive Async Delay
|
dbLockRetry
|
Jet OLEDB:Lock Retry
|
dbUserCommitSync
|
Jet OLEDB:User Commit Sync
|
dbImplicitCommitSync
|
Jet OLEDB:Implicit Commit Sync
|
dbMaxBufferSize
|
Jet OLEDB:Max Buffer Size
|
dbMaxLocksPerFile
|
Jet OLEDB:Max Locks Per File
|
dbLockDelay
|
Jet OLEDB:Lock Delay
|
dbRecycleLVs
|
Jet OLEDB:Recycle Long-Valued Pages
|
dbFlushTransactionTimeout
|
Jet OLEDB:Flush Transaction Timeout
|
EmoticonEmoticon