MDAC 2.5 SDK - Technical Articles


 

Miscellaneous

The next few sections show the differences between DAO and ADO for these tasks:

Handling Errors

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.

Using Transactions

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 that 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.

Compacting a Database

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 that 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. 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

Refreshing the Cache

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. As each application can only have one DBEngine object, it 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 example above 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 when the application is first launched and then at some later point a Recordset with the latest data needs to be opened.