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

Posted on 2011-05-11
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
    LVL 65

    Expert Comment

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

    Author Comment

    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now