Jet Query error 3052

[SharonLove]

Hi!

I am executing a query to update a 2nd Access mdb database with-in a transaction (Update query).
Many records get updated through this query.
I am updating the records in the current mdb using a Join on a table in a 2nd mdb.

This is done with Ado connection:
I am using the jet provider 4.0, but the databases are jet 3.

So, the error says:
3052: File sharing lock count exceeded

How do I need to handle this for a customer receiving this error?

 

[SharonLove]

I'm sorry if I sound impatient, but I was just wondering if no one just hasn't had time to figure this out or give me an answer, or if no one knows what the problem is or how to solve it.

I thank all of you so dearly for your time!

 

[vb5prgrmr]

Not sure if this may be it but I'll ask it anyways...

Are you closing your objects after you are done with them and then reset them for use?

Good Luck

 

[VBOldTimer]

I'm sure the problem has to do with the amount of locks on a single mdb caused by the transaction. Each updated record will cause a lock, and sometimes two or three locks.

The basic number of locks is 9500, which can be depleted fast when using alot of updates with-in a transaction, especially with multiple action queries.

You will need to increase this value using the MaxLocksPerFile setting in the registry for the jet version you are using, or using the setoptions property under dao, or setting the property in the connection property for MaxLocksPerFile (look up the exact syntax under jet provider properties) after the connection is established.
Try setting increasing it in increments of 10,000 until the problem is solved, but you should first estimate and test the transaction with an amount of writes that which you could consider as an average upper limit.
And, for other cases, have an option setting in your program that the user can set to overide this value if needed, for those "seldom" exceptions.

 

[SharonLove]

Changing the value in the registry doesn't work and using Max Locks Per File in code doesn't work, unless you use the same database and provider type, either only Access 97 and provider 3.51, or only Access 2000 and provider 4.0. Then it works.

But the problem is using provider 4.0 and Access 97 mdb.

I do not understand why.

Any one have any further suggestions?

 

[VBOldTimer]

I'm not sure why this doesn't work, or there isn't a solution in code for this situation.

 

[VBOldTimer]

Sorry, I still cannot find an answer, so someone else will need to help out.

 

[rorubin]

Actually the OldTimer was on the right track...check out the Microsoft Support page for the resolution. Follow the instructions and it should work.

http://support.microsoft.com/default.aspx?scid=kb;en-us;173006

 

[rorubin]

Sorry I didn't read the post completely. hee hee. disregard my post.