Error -2147467259 when doing multiuser updates on an Access database from MS Project VBA using ADO.

Posted on 2011-05-11
Medium Priority
Last Modified: 2012-05-11
Receiving error -2147467259 when doing multiuser updates on an Access database from MS Project VBA using ADO.  Any clue why?
Question by:musgah
  • 2
LVL 65

Expert Comment

ID: 35740474
How are you defining your connections and recordsets?
You are remembering to close afterwards right? and setting objects to nothing

Author Comment

ID: 35740664
I should have provided the error message, not just the error number in my question.  The message was in French, but it basically translates to "Impossible to read record.  Currently locked by other user".  This is even stranger by the fact that one is attempting to update the same records simultaneously because the records they are updating are filtered using GUIDs that are user specific.  And by the way, this error message occurs even though the Access database options specify "Open using record-level locking".  Don't know if that might help you or not.

But anyway, to answer your questions :
1) The connections and recordsets are defined using:
    Set cn = CreateObject("ADODB.Connection")
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = cn
    cmd.CommandText = "UPDATE... SET ... WHERE ... AND etc."

2) I am closing the connections and recordsets using rs.Close and cn.Close, but I do not set them to nothing.

LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 35741076
Ok, can you try a different approach? If you use a recordset instead of command text you can specify the lock types

Have a look at the different cursor and lock types. When you encounter a locked record you could simply run the update again for say a set number of times then show error

Dim adoRecSet As ADODB.Recordset

    Set adoRecSet = New ADODB.Recordset
    sSql = "SELECT * FROM MyTable WHERE ..."
    adoRecSet.Open sSql, cn, adOpenDynamic, adLockPessimistic
    If adoRecSet.EOF = False Then
        'EDIT DATA
        adoRecSet!MyField = somevalue
    end if
    Set adoRecSet = Nothing
    Set cn = Nothing

Open in new window


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question