ACC2: TransferDatabase Fails in Code, Not in Immediate Window (117612)

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use the TransferDatabase action within a transaction in an Access Basic function, you receive the error message:

Couldn't update, locked by another user on this system.

   -or-

Couldn't update, currently locked by another session on this machine.

However, if you set a breakpoint in the function and single-step through it, you do not receive the error message.

CAUSE

This error occurs only when you run a TransferDatabase action nested in a transaction on a table that is already attached to Microsoft Access.

The error occurs because a transaction updates the MySysObjects table, placing a write lock on the table. The write lock is not released while the transaction is still active. The DoCmd TransferDatabase statement causes Microsoft Access to start a new session and try to create a new table. However, the Microsoft Jet database engine needs to update the MySysObjects table because it is adding a record, but the MySysObjects table is still locked.

RESOLUTION

Do not use nested TransferDatabase actions in transactions against attached tables. Instead, use the following techniques:

Move the TransferDatabase action above or below the transaction.
Move the data from the attached table into a local table and perform the TransferDatabase action on it instead.

MORE INFORMATION

Steps to Reproduce Problem

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

Start Microsoft Access and create a new database.
Import the Employees table from the sample database NWIND.MDB into the new database.
Attach the Order Details table from NWIND.MDB.

Create a new module and enter the following procedure:

      Function TestIt ()
         Dim ws as Workspace
         Dim db As Database, rs As Recordset
         BeginTrans
            set ws=dbengine.workspaces(0)
            Set db = ws.databases(0)
            Set rs = db.OpenRecordset("Order Details", DB_OPEN_DYNASET)
            rs.FindFirst "[Order Id]=10010"
            rs.Edit
            rs![Order Id] = 10001
            rs.Update
            DoCmd TransferDatabase A_EXPORT, "Microsoft Access",_
             "C:\ACCESS\SAMPAPPS\NWIND.MDB", A_TABLE, "Employees",_
             "Employees2", False
         CommitTrans
      End Function

From the View menu, choose Immediate Window.
In the Immediate window, type the following line and then press ENTER:

? TestIt()