Access Queries RecordLocks

Hi Experts! I need some vba code to loop/iterate through all queries in a db and change the recordlocks to "No Locks"

I think I need to open the query in design view and then save it??

Thanks for any help you can provide. Thanks.
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This is a little more than you need, but it show you how to do some other things as well.


Function DoAllQueryDefs()

    Dim db As Database
    Dim myquerydef As QueryDef
    Dim intI As Integer
    Dim intJ As Integer
    Dim intRet As Integer

    Dim strOLDSQL As String
    Dim strNewSQL As String
    ' DAO method

    Set db = DBEngine.Workspaces(0).Databases(0)

    For intI = 0 To db.QueryDefs.Count - 1
        Set myquerydef = db.QueryDefs(intI)
        Debug.Print "> "; myquerydef.Name
        myquerydef.Properties("RecordLocks") = 0
        ' Loop through properties
        'For intJ = 0 To myQueryDef.Properties.count - 1
        '    Debug.Print ">>> "; myQueryDef.Properties(intJ).Name;
        '    Debug.Print ":"; myQueryDef.Properties(intJ)
        'Next intJ
        ' Set premissions for a group.
        'intRet = glrChangePermission(7,, "USERS", DB_SEC_RETRIEVEDATA, False)
        'Debug.Print intRet
        ' Modify SQL string
        'strOLDSQL = myquerydef.Properties("SQL")
        'If InStr(strOLDSQL, "WITH OWNERACCESS OPTION") = 0 Then
        'Debug.Print ">>> "; myquerydef.Properties("SQL").Name;
        '    strNewSQL = Mid(strOLDSQL, 1, Len(strOLDSQL) - 3) & " WITH OWNERACCESS OPTION" & Mid(strOLDSQL, Len(strOLDSQL) - 2, 3)
        '    myquerydef.Properties("SQL") = strNewSQL
        'End If

    Next intI

End Function

Open in new window

Eileen MurphyIndependent Application DeveloperAuthor Commented:
Hey Jim; thank you for this. I ran the code - no errors - but when I open random queries the Record Locks remain set to "Edited Record". I noticed all action queries were the ones which remained set to "Edited Record" as "No Locks" is unavailable as an option.

I never noticed this before. Any thoughts on why this is?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It is because no locks is actually a misnomer. What no locks means is no edit record locks. It does not mean that no locks are ever placed.  You always place a lock when you touch a  record.

The difference between the two is when the lock is placed.  An edit record lock places the lock soon as you start to change the record and is not released  until  you save it.  This is referred to as pessimistic locking. When you don't use an edit record lock, the lock does not get placed until you actually go to save the record. This is referred to as  opportunistic locking.

 The latter gives you less concurrency issues, but you run the risk of two users trying to edit the same record at the same time. If that happens, one will lose their changes.

 But with an action query, there is no way for no edit locks to be placed because you are  always saving a record immediately ( more or less)

What you may want to look at however is the use transaction property. If this is set to yes all locks are held for the time the entire query runs. This allows you to roll back in the middle of a query. If you change us to know no, As soon as the lock limit his reached, which by default is 9500, all locks are released. This yields less concurrency issues, but if you're query aborts in the middle for some reason, you are left with and indeterminant number of records that have been updated. If you can re-execute the query without a problem then setting the use transaction property to no is the way to go.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks a lot Jim!!!!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Hopefully you looked at that after I did all the edits.   My phone stuck in a lot of weird stuff.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.