DBEngine.SetOption & the registry

[David Mueller]

Hi All,

I'm using A2003 and I'm trying to use the DBEngine.SetOption and it just
isn't working for me. I have DAO 3.6 referenced. No other DAOs are
referenced. It's the first statement after my DIM statements in my function.

DBEngine.SetOption dbMaxLocksPerFile, 500000

If I manually modify the registry, my transactions work fine. The following
in the registry fixes my transaction/workspace problem...

Jet/4.0/Jet 3.x (MaxLocksPerFile) = 500000 (this is the one I change)
Jet/4.0/Jet 4.0 (MaxLocksPerFile) = 9500

I set the registry back to the default 9500 and issue the DBEngine.SetOption
in code, ... and my transactions will "system resource exceeded" #3035. I
need to use the SetOption method because the registry is locked down in my
organization.

Any thoughts on this? Is there a way to verify if the SetOption method is
successfully modifying the session?

Thanks
David

 

[david]

Are you connecting to/using an enabled (Access 97) Jet 3.5 mdb?

I can't think of any other reason why the Jet 4/3.5 engine
would be in use. Can you try converting the database
to Jet 4.0?

(david)

 

[David Mueller]

Thanks. I am linked to an A97 database for this particular transaction. The
A97 database is used by a legacy system, over which I have no control; I just
feed it data via the A97 database.

I'm not sure if I can get away with converting it to A2003, running my
transactions, and then converting it back to A97.

I suppose there may not be a way to override the MaxLocksPerFile in this
situation. I might have to consider abandoning the transaction/workspace.

Any ideas to help me keep the transaction would be appreciated.

Thanks again,
David

 

[david]

That is interesting. I've never seen anyone discuss this behaviour
before, and I don't know if there is a best way around it.

I'm guessing, since none of the MVP's have jumped in, that they
haven't considered this problem either.

Re-Converting the target database would probably be the last thing
I would do, after reconsidering the transaction to see if I could
make it either more efficient, or less efficient (another intermediate
table or database or workspace or application). (Note, you
are presumably using the default Application.dbEngine).

Conversion in one direction might be reasonable, but back and
forth just looks sad. However, check that your legacy application
really needs an A97 database - some legacy products just use
A97 databases so that they can support legacy attachments...

(david)