Problem with: DBEngine.SetOption dbMaxLocksPerFile, 500000
David Mueller
I need to use the DBEngine.SetOption to modify MaxLocksPerFile registry
entry. It just isn't working for me. If I manually change the registry, it
works. But, when I issue the command through VBA, it doesn't work (doesn't
err, either)
My SQL is inside a BeginTrans and CommitTrans block, such as:
Dim wsp As DAO.Workspace
Dim db_wsp As DAO.Database
Set wsp = DBEngine.Workspaces(0) 'set the workspace
Set db_wsp = wsp(0)
wsp.BeginTrans
db_wsp.execute "update ...."
wsp.CommitTrans
I've put the code "DBEngine.SetOption dbMaxLocksPerFile, 500000" everywhere
in my code. Literally. It doesn't seem to matter. I even tried
"Application.DBEngine ..." and sprinkled "DAO.DBEngine.Idle dbRefreshCache"
all about.
The last thing I tried was to execute my SQL outside the transaction... and
that worked but doesn't do me any good.
Can anyone tell me what I'm missing here?
Thanks
David
david
set up, with a Jet 3.5 BE, and you should make the
change to the registry, rather than using SetOption.
If not, here is a longer message:
What is the problem?
Not that I think I can help you, but
Which version of Access are you using?
What error message are you getting?
What kind of database are you using?
What version is the database?
What version of Windows are you using?
How do you connect to the database/where is it?
What is the SQL/What is it doing?
What kind of recordset/curser
Both transactions and Locks are features that have
been modified ie improved ie broken since the
original design of Access/Jet (I continue to have
great respect for the original designers of Access/Jet)
Both Transactions and Locks are features that have
always been under-documented, but did reach a
high point of proper documentation with A95/A97.
The documented behaviour of A97 (that is, the
little known documented behaviour of A97) was
that transactions would partially commit when the
MaxLocksPerFile setting was exceded. (If you
think that is odd behaviour for a transaction,
well, it was a last minute kludge, and they didn't
really tell anyone)
The SetOption method is specifically supposed to
work correctly for transactions. Given the history of
the dbMaxLocksPerFile property, it is of course
possible that MS has "fixed" it again, and again failed
to tell everyone.
Other posibilities are that you really are running out
of disk space, or are running into permission problems
with the tmp file, or are just having update errors,
or too many unique-index clashes, or MaxBufferSize
too small, or broken SMB (unpatched server), or
using mixed versions or... what else can you tell us
about the problem?
(david)
"David Mueller" <DavidM...@discussions.microsoft.com> wrote in message
news:5D684457-12C8-455D...@microsoft.com...