Increase ADO Recordlocks in code

I have found that the following works to increase recordlocks when using DAO file handling

DAO.DBEngine.SetOption dbMaxLocksPerFile, 300000

Is there a way to increase the record locks while using ADO file handling?  I tried

ADO.DBEngine.SetOption dbMaxLocksPerFile, 300000

but the compiler didn't like that.
LVL 1
mlcktmguyAsked:
Who is Participating?

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

x
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You have to set this in the Connection object:

Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Properties("Jet OLEDB:Max Locks Per File") = 20000

You'd then use that "con" object when creating recordsets and such:

Dim rst As New ADODB.Recordset
rst.ActiveConnection = con
<etc etc>
rst.Open "SELECT * FROM SomeTable", con

See this EE question for more information:

http://www.experts-exchange.com/Database/MS_Access/Q_24217351.html

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
mlcktmguyAuthor Commented:
Thanks, after looking thru the linked article I used
Dim rsIn2 As ADODB.Recordset
'
CurrentProject.Connection.Properties("Jet OLEDB:Max Locks Per File") = 400000
'
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Open in new window


It worked.
All of my tables are linked to the backend so I don't explicitly establish a connection anywhere in the code.

Does the 'CurrentProject.Connection.Properties("Jet OLEDB:Max Locks Per File") = 400000' statement have to be used each time a recordset is opened.  Or, since all of my tables are linked can I set it once in the code and it will apply to any recordet that is opened.?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, you would need to do this each time. CurrentProject.Connection returns a reference to the actual Access Connection object, so you cannot permanently change it.
mlcktmguyAuthor Commented:
Excellent, thank you.
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.