My Access Tips for Custom Microsoft Access

There is a corruption in the database application.
The record has been locked by a user and Microsoft Access or another user is attempting to modify the record.
VBA code has opened a recordset and has a lock on a record.
Two or more instances of the Microsoft Access application is running on the same PC.
The application is using page record locking and has exceeded the 4K limit (2K limit for Jet database engine version 3.5x and earlier).
The back-end application resides on a Novell Server and the maximum record locks has been exceeded.

Here are some tips for resolving the issue.

Design Tips

Information on designing multi-user applications in MS Access

Split the database into a Back-end and Front-end

Splitting the application is t is extremely important in a multiple user environment.
http://www.databasedev.co.uk/split_a_database.html

Distribute a Front-end MDE instead of an MDB

A MDE file is a complied version of the mdb that locks down modules, reduces database size, and increases performance.
http://www.databasedev.co.uk/microsoft-access-mde.html

Deploy the Front-end application to every users local PC

Providing each user with their own copy of the FE application will increase the applications performance, increase stability and reduce corruptions.
http://www.myaccesstips.com/deploy/deploy.html

Do not run update queries on records that the user(s) are (or could be) currently editing.

http://support.microsoft.com/kb/304181

Make sure the Record Locks property is set to none for queries, forms, and reports.

The example code provided will loop through the Form and Report objects of an mdb file and print to the debug window any objects where Record Locks property is not “none”. Paste the code to a new module and run it typing the procedure name (CheckLockProperty) in the debug (immediate window).

Function CheckLockProperty()
'Prints object to immediate window where the Record Locks property is not set to 'None'
    Dim frm As Object
    Dim rpt As Object
 
    For Each frm In Application.CurrentProject.AllForms
        With frm
            DoCmd.OpenForm .Name, acDesign, , , , acHidden
            If Forms(.Name).RecordLocks <> 0 Then
                Debug.Print "Form: " & .Name
            End If
            DoCmd.Close acForm, .Name
        End With
    Next frm
            
    For Each rpt In Application.CurrentProject.AllReports
        With rpt
            DoCmd.OpenReport .Name, acDesign, , , acHidden
            If Reports(.Name).RecordLocks <> 0 Then
                Debug.Print "Report: " & .Name
            End If
            DoCmd.Close acReport, .Name
        End With
    Next rpt
 End Function

Verify each user’s installation of MS Access is set to Record Level Locking.

For Jet 4.0 and higher (Access 2000 or higher)
From the menubar of MS Access select Tools > Options. In the Advanced tab set the following:
Default Record Locking: No Locks
Check the checkbox for “Open Database using record level locking”

Avoid binding data entry forms to a multi-table query.

Forms where the user will add or edit data should be bound to a table or a single table query. If the query has more then 1 table in it, then create a sub-form for each table needed in the query.
http://support.microsoft.com/kb/837937

Restructure tables to reduce the size of the records (especially Memo and OLE fields).

From Microsoft “Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked.”
http://msdn2.microsoft.com/en-us/library/aa189633(office.10).aspx

Move memo and OLE fields to a separate table, create a 1 to 1 relationship, and use a subform.

Avoid using memo and OLE filed as much as possible. If you have to use them, move the memo and OLE fields to another table. Create a 1 to 1 relationship, enforce referential integrity, with cascade updates and deletes.

Look at the following table structure:

tblCustomers
-CustomerID – (Autonumber, PK)
-CustomerName – (Text)
-Address – (Text)
-City – (Text)
-State – (Text)
-Zip – (Text)
-CustomerNotes – (Memo)

Separating the memo field into another table would look like these:

tblCustomers
-CustomerID – (AutoNumber, PK)
-CustomerName – (Text)
-Address – (Text)
-City – (Text)
-State – (Text)
-Zip – (Text)

tblCustomerNotes
-CustomerID – (Long, PK)
-CustomerNotes – (Memo)

Use a main form for the tblCustomers table and a subform for the tblCustomerNotes table.

logically separate data into different tables

To avoid conflicts, logically separate data into different tables if the data is to be updated by different users.

Consider the following table structure (I realize the table is not normalized, but this makes for an easy illustration).

tblCustomers
-CustomerID – (AutoNumber, PK)
-CustomerName – (Text)
-BillToAddress – (Text)
-BillToCity – (Text)
-BillToState – (Text)
-BillToZip – (Text)
-ShipToAddress – (Text)
-ShipToCity – (Text)
-ShipToState – (Text)
-ShipToZip – (Text)

If the shipping address is maintained by customer service and the billing address is maintained by the accounting department, a locking conflict would occur if a user from each department were trying to update the same vendor at the same time.

Separating the data into 2 different tables would eliminate this locking issue.

tblCustomers
-CustomerID – (AutoNumber, PK)
-CustomerName – (Text)
-BillToAddress – (Text)
-BillToCity – (Text)
-BillToState – (Text)
-BillToZip – (Text)

tblCustomersShipping
-CustomerID – (Long, PK)
-ShipToAddress – (Text)
-ShipToCity – (Text)
-ShipToState – (Text)
-ShipToZip – (Text)

Resolving Corruptions Tips

Compact and Repair the Back-end data file and Front-end application.

This will repair errors and reduce the size of the application and back-end data file. The Backend data file is easily overlooked by developers new to MS Access. Make sure to backup the mdb files before running this command (Tools > Database Utilities > Compact and Repair).
http://office.microsoft.com/en-us/access/HP051874491033.aspx

Decomplie the Front-end application to reduce corruptions.

This quick routine has fixed many unexplained reoccurring error in my applications.
http://www.fmsinc.com/free/newtips/access/accesstip7.asp

Use the JetComp to recover a corrupted database.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;273956

My Access Tips for Custom Microsoft Access

Application Development

by Matthew V Carmichael

Need Help?

Record Locking errors in Microsoft Access applications.

Error 3218 "Could not update; currently locked."

Error 3197 "The database engine stopped the process because you and another user are attempting to change the same data at the same time."

Error 3260 "Couldn't update; currently locked by user <name> on machine <name>."

Possible Causes:

Here are some tips for resolving the issue.

Design Tips

Resolving Corruptions Tips

Knowledge Base Articles

Design Tips

Information on designing multi-user applications in MS Access

Split the database into a Back-end and Front-end

Distribute a Front-end MDE instead of an MDB

Deploy the Front-end application to every users local PC

Do not run update queries on records that the user(s) are (or could be) currently editing.

Make sure the Record Locks property is set to none for queries, forms, and reports.

Verify each user’s installation of MS Access is set to Record Level Locking.

Avoid binding data entry forms to a multi-table query.

Restructure tables to reduce the size of the records (especially Memo and OLE fields).

Move memo and OLE fields to a separate table, create a 1 to 1 relationship, and use a subform.

logically separate data into different tables

Resolving Corruptions Tips

Compact and Repair the Back-end data file and Front-end application.

Decomplie the Front-end application to reduce corruptions.

Use the JetComp to recover a corrupted database.