[AccessD] Cannot open any more databases

Very Interesting indeed. I have struggled with the 3048 error for quite a
while. I'm curious about the extended testing..


WBR
 ~Robert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, May 30, 2008 12:13 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Cannot open any more databases

In testing the whole issue of memos and locking I did the 
following:

1) Created a FE and a BE.
2) Created a single tblMemo in the Be. Two fields, an 
autonumber field PKID and a memo field SomeMemo.
3) Linked the FE to the BE on tblMemo.
4) Added 1280 records in tblMemo with SomeMemo set to a 
string of 200 character a.
5) Set the locking to Record Level on both the FE and BE.

Wrote a pair of classes in the FE. clsRst and clsRsts 
(plural).  clsRsts opens a DAO recordset of the tblMemo, 
then starts iterating the recordset, creates instances of 
clsRst and stores the instances in a collection.

ClsRst is passed in a DAO.Db reference (to use in opening a 
recordset) and PKID from the record that clsRsts is 
currently on.  Thus an instance of clsRst is created for 
each record in tblMemo, doing a moveFirst to the PKID passed 
in, then attempting to edit the record (memo field), thus 
creating a lock.

On the 119th instance it fails with an error:

"Error 3048 (Cannot open any more databases.) in procedure 
mInit of Class Module clsRst"

ONLY 119.  Hmmm... I was expecting well over a thousand.

Now comes the interesting part.  The collection shows a 
count of objects in it of 118.  However if IN THE FRONT END 
I open the table directly and place the cursor in the first 
record I see it is locked.  I start paging down through the 
records.  I see locked records clear up to record 178. 
Record 179 is NOT locked.  So... I am holding 118 open 
recordsets in 118 class instances.  Each of these recordsets 
is attempting an edit.  However 177 records end up locked.

Now, it is possible that other records are locked further 
down in the table, I have not attempted to look at every 
record (there are 1280 after all).

However the fact that 118 recordsets editing 118 records 
locks 177 records speaks volumes about something.  Not sure 
WHAT but something.

Even more fascinating, if IN THE BACK END I start paging 
down through the records, the records indicate they are 
locked up to record 118, but CEASE to show locks starting 
with record 119.

WTF over?  Records 119-177 show locks IN THE FE but NOT IN 
THE BE.

Additionally if I move down through the remaining records, 
IN THE FE I encounter other locked records, roughly every 
5th record, though occasionally every sixth record.

This does NOT occur in the BE!

Is this bizaare or what?

It will be interesting to see what happens if I open the FE 
again (another instance).

My next experiment will be to open a (new, different) single 
recordset and iterate through the records attempting an 
edit, and log all the PKs where the code indicates an 
existing lock.

I also think I will get "more scientific" and seed the table 
with different fixed width strings.  For example run the 
code with memo strings of 20 characters, 50, 100, 200 etc. 
Just to see if the locking is repeatable, and if a pattern 
can be determined.

But not tonight.

Tell me your thoughts on this.
-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com