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