The next few sections show the differences between DAO and ADO for these tasks:
There are two types of errors that can occur when executing ADO, ADOX, or JRO code: ADO errors and provider errors. ADO errors occur when you attempt to perform an invalid operation, such as trying to retrieve the tenth Field from the Recordset object's Field collection when the Fields collection only contains five fields.
Provider errors are errors generated by the OLE DB Provider or underlying data source. For example, specifying an invalid file name as the data source when trying to open a Microsoft Jet database will result in a provider error.
ADO errors are exposed by the run-time exception handling mechanism. In Visual Basic for Applications, an ADO error will trigger the On Error event and the Err object will contain information about the error. The ADO error will not create a new Error object in the Errors collection of the ADO Connection. OLE DB Provider errors will create new Error objects in the Errors Collection of the ADO Connection.
The Error object in both DAO and ADO is unlike the error variables and functions in Visual Basic in that more than one error can be generated by a single operation. The set of Error objects in the Errors collection describes one error.
The following code attempts to open a database that doesn't exist and then displays the error(s) that result.
DAO
Sub DAODatabaseError()
On Error GoTo DAODatabaseError_Err
Dim db As DAO.Database
Dim errDB As DAO.Error
Set db = DBEngine.OpenDatabase(".\NonExistent.mdb")
Exit Sub
DAODatabaseError_Err:
For Each errDB In DBEngine.Errors
Debug.Print "Description: " & errDB.Description
Debug.Print "Number: " & errDB.Number
Debug.Print "JetErr: " & errDB.Number
Next
End Sub
ADO
Sub ADODatabaseError()
On Error GoTo ADODatabaseError_Err
Dim cnn As New ADODB.Connection
Dim errDB As ADODB.Error
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NonExistent.mdb"
Exit Sub
ADODatabaseError_Err:
For Each errDB In cnn.Errors
Debug.Print "Description: " & errDB.Description
Debug.Print "Number: " & errDB.Number & " (" & _
Hex$(errDB.Number) & ")"
Debug.Print "JetErr: " & errDB.SQLState
Next
End Sub
The code is very similar. Note, however, that the ADO code will print two different error numbers. The first number is the ADO/OLE DB error code. This error code will be the same for similar errors regardless of the provider being used. This allows you to write ADO applications that can handle errors even when the provider is changed. The second number is a provider-specific error code. When using the Microsoft Jet Provider, this error number will be the same error number that DAO returns. However, other providers may return different numbers for this type of error.
A transaction is defined as a "logical unit of work." Use transactions to enforce data integrity by making sure that multiple, related database operations are committed in an all-or-nothing manner. Microsoft Jet allows you to include both DML and DDL operations within a single transaction.
The following listing demonstrates how to use a transaction. It combines DML and DDL operations within a single transaction. If any part of the code fails, all changes will be rolled back. The code creates a new table named Contacts, populates it with data from the Customers table, adds a new column named ContactId to the Customers table, and then deletes the columns containing contact information from the Customers table.
DAO
Sub DAOTransactions()
On Error GoTo DAOTransactions_Err
Dim wks As DAO.Workspace
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim bTrans As Boolean
' Get the default workspace
Set wks = DBEngine.Workspaces(0)
' Open the database
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Begin the Transaction
wks.BeginTrans
bTrans = True
' Create the Contacts table.
Set tbl = db.CreateTableDef("Contacts")
With tbl
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
.Fields.Append .CreateField("ContactId", dbLong)
.Fields("ContactId").Attributes = dbAutoIncrField
.Fields.Append .CreateField("ContactName", dbText)
.Fields.Append .CreateField("ContactTitle", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)
.Fields("Notes").Required = False
End With
db.TableDefs.Append tbl
' Populate the Contacts table with information from the
' customers table
db.Execute "INSERT INTO Contacts (ContactName, ContactTitle," & _
"Phone) SELECT DISTINCTROW [Customers].[ContactName], " & _
"[Customers].[ContactTitle], [Customers].[Phone] " & _
"FROM Customers;"
' Add a ContactId field to the Customers Table
Set tbl = db.TableDefs("Customers")
tbl.Fields.Append tbl.CreateField("ContactId", dbLong)
' Populate the Customers table with the appropriate ContactId
db.Execute "UPDATE DISTINCTROW Contacts INNER JOIN Customers " & _
"ON Contacts.ContactName = Customers.ContactName SET " & _
"Customers.ContactId = [Contacts].[ContactId];"
' Delete the ContactName, ContactTitle, and Phone columns from
' Customers
tbl.Fields.Delete "ContactName"
tbl.Fields.Delete "ContactTitle"
tbl.Fields.Delete "Phone"
' Commit the transaction
wks.CommitTrans
Exit Sub
DAOTransactions_Err:
If bTrans Then wks.Rollback
Debug.Print DBEngine.Errors(0).Description
Debug.Print DBEngine.Errors(0).Number
End Sub
ADO
Sub ADOTransactions()
On Error GoTo ADOTransactions_Err
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim bTrans As Boolean
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Begin the Transaction
cnn.BeginTrans
bTrans = True
Set cat.ActiveConnection = cnn
' Create the Contacts table
With tbl
.Name = "Contacts"
Set .ParentCatalog = cat
.Columns.Append "ContactId", adInteger
.Columns("ContactId").Properties("AutoIncrement") = True
.Columns.Append "ContactName", adWChar
.Columns.Append "ContactTitle", adWChar
.Columns.Append "Phone", adWChar
.Columns.Append "Notes", adLongVarWChar
.Columns("Notes").Attributes = adColNullable
End With
cat.Tables.Append tbl
' Populate the Contacts table with information from the
' customers table
cnn.Execute "INSERT INTO Contacts (ContactName, ContactTitle," & _
"Phone) SELECT DISTINCTROW Customers.ContactName," & _
"Customers.ContactTitle, Customers.Phone FROM Customers;"
' Add a ContactId field to the Customers Table
Set tbl = cat.Tables("Customers")
tbl.Columns.Append "ContactId", adInteger
' Populate the Customers table with the appropriate ContactId
cnn.Execute "UPDATE DISTINCTROW Contacts INNER JOIN Customers " _
& "ON Contacts.ContactName = Customers.ContactName SET " & _
"Customers.ContactId = [Contacts].[ContactId];"
' Delete the ContactName, ContactTitle, and Phone columns
' from Customers
tbl.Columns.Delete "ContactName"
tbl.Columns.Delete "ContactTitle"
tbl.Columns.Delete "Phone"
' Commit the transaction
cnn.CommitTrans
Exit Sub
ADOTransactions_Err:
If bTrans Then cnn.RollbackTrans
Debug.Print cnn.Errors(0).Description
Debug.Print cnn.Errors(0).Number
Debug.Print cnn.Errors(0).SQLState
End Sub
Both DAO and ADO have similar methods for beginning, committing, and rolling back a transaction. One difference to note, however, is that because DAO transactions are tied to the Workspace object, it is possible to use DAO to perform a transaction that spans multiple Microsoft Jet databases. ADO transactions are tied to the Connection object, which limits the transaction to a single data source.
DAO also supports an additional parameter to the CommitTrans method: dbForceOSFlush. This forces the database engine to immediately flush all updates to disk, instead of caching them temporarily. The Microsoft Jet Provider exposes a property, "Jet OLEDB:Transaction Commit Mode," in the Connection object's Properties collection, which allows you to specify that transactions within that connection should flush all updates to disk upon commit. Setting this property to 1 is equivalent to using the dbForceOSFlush parameter.
As a database file is used, it can become fragmented as objects and records are created and deleted. Periodic defragmentation reduces the amount of wasted space in the file and can enhance performance. Compacting can also repair a corrupted database.
The following listings demonstrate how to compact a database.
Note Use JRO, not ADO, to compact a database.
DAO
Sub DAOCompactDatabase()
' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(".\NewNorthWind.mdb") <> "" Then Kill ".\NewNorthWind.mdb"
' Basic compact - creating new database named newnwind
DBEngine.CompactDatabase ".\NorthWind.mdb", ".\NewNorthWind.mdb"
' Delete the original database
Kill ".\NorthWind.mdb"
' Rename the file back to the original name
Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
JRO
Sub JROCompactDatabase()
Dim je As New JRO.JetEngine
' Make sure there isn't already a file with the
' name of the compacted database.
If Dir(".\NewNorthWind.mdb") <> "" Then Kill ".\NewNorthWind.mdb"
' Compact the database
je.CompactDatabase "Data Source=.\NorthWind.mdb;", _
"Data Source=.\NewNorthWind.mdb;"
' Delete the original database
Kill ".\NorthWind.mdb"
' Rename the file back to the original name
Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
The JRO CompactDatabase method takes two connection strings, which indicate the source database and destination database respectively. See the JRO online Help for more information on the JRO CompactDatabase method.
In addition to defragmenting or repairing your database, CompactDatabase can also be used to change the database password, convert the database from an older Microsoft Jet version to a new version, to encrypt or decrypt the database, or to change the locale of the database. Note that CompactDatabase will automatically convert older databases to the current version, unless you use the Jet OLEDB:Engine Type property in the destination database parameter.
The following code demonstrates how to encrypt a database.
DAO
Sub DAOEncryptDatabase()
' Use compact to create a new, encrypted version of the database
DBEngine.CompactDatabase ".\NorthWind.mdb", _
".\NewNorthWind.mdb", , dbEncrypt
End Sub
JRO
Sub JROEncryptDatabase()
Dim je As New JRO.JetEngine
' Use compact to create a new, encrypted version of the database
je.CompactDatabase "Data Source=.\NorthWind.mdb;", _
"Data Source=.\NewNorthWind.mdb;" & _
"Jet OLEDB:Encrypt Database=True"
End Sub
Microsoft Jet maintains an internal cache of records for each Microsoft Jet session. Caching records provides a significant performance improvement, but it means that other sessions may not immediately see changes.
In DAO a session is associated with a DBEngine object. Because each application can only have one DBEngine object, this means that each application will have its own session. A given application using DAO will always see its own changes, but other applications may not see the changes immediately. In ADO a session is associated with a Connection object. A single application using ADO may have multiple Connection objects. So, within a single application, changes may not been seen immediately.
There may be instances where performance is less important than guaranteeing that a Recordset contains the latest data. In those instances, it makes sense to force a refresh of Microsoft Jet's internal cache. Both DAO and JRO provide a mechanism for this. In DAO use the DBEngine object's Idle method with dbRefreshCache to force Microsoft Jet to refresh its cache. With JRO use the JetEngine object's RefreshCache method, passing in the ADO connection as a parameter.
The following listings demonstrate how to refresh the cache using DAO and JRO.
DAO
Sub DAORefreshCache()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Refresh the cache to ensure that the latest data
' is available.
DBEngine.Idle dbRefreshCache
Set rst = db.OpenRecordset("SELECT * FROM Shippers")
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value;
Next
Debug.Print
rst.MoveNext
Loop
rst.Close
End Sub
ADO
Sub JRORefreshCache()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim je As New JRO.JetEngine
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Refresh the cache to ensure that the latest data
' is available.
je.RefreshCache cnn
' Open a recordset and read the data
Set rst = cnn.Execute("SELECT * FROM Shippers")
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value;
Next
Debug.Print
rst.MoveNext
Loop
rst.Close
End Sub
This preceding example is somewhat contrived because the cache will most likely already contain the latest data as the Database and Connection are being opened for the first time immediately before attempting to open the Recordset. The ability to refresh the cache is generally more useful when a Database or Connection is opened at the time the application is first launched, and then at some later point a Recordset with the latest data needs to be opened.