PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60

Article ID: 306435 - View products that this article applies to.
This article was previously published under Q306435
Expand all | Collapse all

On This Page

SYMPTOMS

According to Microsoft Knowledge Base article 275561
(http://support.microsoft.com/kb/275561/EN-US/ )
"ACC2000: New Features in Microsoft Jet 4.0":
To minimize the impact of the increased page size and respond to a long-standing request from developers building applications based on the Microsoft Jet database engine, row-level locking was added to Jet 4.0.
However, row-level locking of an Access database is not available with Data Access Objects (DAO) 3.60.
Back to the top | Give Feedback

RESOLUTION

To resolve this problem, use ActiveX Data Objects (ADO) to enable row-level locking on an Access database, and then open DAO connections to the database. All subsequent attempts to open DAO connections to the database will respect the locking mode that you set.
Back to the top | Give Feedback

STATUS

This behavior is by design. DAO 3.60 was not updated to incorporate this Microsoft Jet 4.0 functionality.
Back to the top | Give Feedback

MORE INFORMATION

The first user to open an Access database determines which mode is used while the database is open. Thus, if the first user uses row-level locking to open the database, users that subsequently open the database will use row-level locking.

When you use the Microsoft OLE DB Provider for Jet 4.0, you can configure row-level locking from within ADO. To enable row-level locking by using ADO, select the Jet 4 Provider, and then set the Connection object's dynamic Jet OLEDB:Database Locking Mode property to 1.

To enforce DAO to use the row-level locking that ADO sets, follow these steps:
  1. Use row-level locking to open an ADO Connection to the database as follows:
    1. Set the ADO Connection object's Provider property to Microsoft.JET.OLEDB.4.0.
    2. Set the Connection object's dynamic Properties("Jet OLEDB:Database Locking Mode") to 1.
    3. Open the ADO Connection.
  2. Use the OpenDatabase method to open the same database from DAO.

    Because the locking mode is reset when you close and reopen the database, use a DAO database that remains open as long as you need row-level locking. For example, use Form or Module level scope in Visual Basic for the DAO database.
  3. Close the ADO Connection.

Sample Code

The code sample to follow demonstrates the sequence of statements to configure row-level locking by using ADO and then opens a DAO connection to the database by using row-level locking. In this sample, the DAO database is declared at Form level so that all DAO databases that are opened within the Form use row-level locking. In addition, this sample uses the Access 2000 Northwind.mdb sample database, which is copied to a folder named C:\MDB.
  1. In Visual Basic, create a new Standard EXE project. Form1 is created by default.
  2. From the Project menu, click References, and then select the following check boxes:
    Microsoft ActiveX Data Objects Library
    Microsoft DAO 3.6 Object Library
  3. Paste the following code into Form1's Code window:
    'This sample uses Form level scope for the open DAO database.
'Depending on your application, you may choose Module level.
Private wsDAO As DAO.Workspace
Private dbDAO As DAO.Database

Private Sub Form_Load()
   Dim cnn As ADODB.Connection

  'ADO has the ability to open row-level locking; DAO does not.
  'The following code is used to implement row-level locking in DAO.
  'If the database is opened first in row-level locking in ADO,
  'subsequent attempts to open the database in ADO and DAO will use the same mode.

   Set cnn = New ADODB.Connection
   cnn.Provider = "Microsoft.JET.OLEDB.4.0"
   cnn.Properties("Data Source") = "C:\MDB\Northwind.mdb"
   cnn.Properties("Jet OLEDB:Database Locking Mode") = 1
   cnn.CursorLocation = adUseServer
   cnn.Open

   Set wsDAO = DBEngine.CreateWorkspace("WorkSpace", "Admin", "", dbUseJet)
   Set dbDAO = wsDAO.OpenDatabase("C:\MDB\Northwind.mdb")
   
   'Close the ADODB connection.
   cnn.Close
   Set cnn = Nothing
End Sub
Back to the top | Give Feedback

REFERENCES

For more information, see the following MSDN Web sites:
Page-Level Locking vs. Record-Level Locking
http://msdn.microsoft.com/en-us/library/aa189633.aspx
(http://msdn.microsoft.com/en-us/library/aa189633.aspx)


Microsoft OLE DB Provider for Microsoft Jet
http://msdn.microsoft.com/en-us/library/ms810660.aspx
(http://msdn.microsoft.com/en-us/library/ms810660.aspx)
Back to the top | Give Feedback

Properties

Article ID: 306435 - Last Review: June 25, 2004 - Revision: 4.3
APPLIES TO
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Data Access Components 2.5 Service Pack 2
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbcodesnippet kbdatabase kbjet kbmdacnosweep kbprb KB306435
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top