ACC2: TransferDatabase Fails in Code, Not in Immediate Window

• Microsoft Access version 2.0

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.

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.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

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.

1. Start Microsoft Access and create a new database.

2. Import the Employees table from the sample database NWIND.MDB into the new database.

3. Attach the Order Details table from NWIND.MDB.

4. Create a new module and enter the following Sub procedure:

         Sub 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 Sub
   
   

5. From the View menu, choose Immediate Window.

6. In the Immediate window, type the following line and then press ENTER:

         ? TestIt
   

REFERENCES

For more information about the TransferDatabase action, search for "TransferDatabase," and then "TransferDatabase Action" using the Microsoft Access Help menu.