Microsoft KB Archive/117612

= ACC2: TransferDatabase Fails in Code, Not in Immediate Window =

Article ID: 117612

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q117612





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.



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.



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 </li> From the View menu, choose Immediate Window.</li> In the Immediate window, type the following line and then press ENTER:

? TestIt

</li></ol>

<div class="references_section">