[AccessD] Cannot open any more databases

I am working on it now.  I am adding a tester class to 
instantiate clsRsts for multiple tables.  One thing I now 
need to test as well is whether the memo field edits can 
indeed lock records in other tables.

I will be creating and seeding two tables with data, 
alternating writing records to the two tables to maximize 
the probability that memos from the two tables are placed 
into the same page (assuming that does indeed happen).

Once I have N records in each table, I will then edit as 
many records as possible in one table and then iterate 
through BOTH tables to test for locked records, and record 
the results in a tblTestResults.

I should have results in a couple of hours.

John W. Colby
www.ColbyConsulting.com


Robert wrote:
> 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.