[AccessD] Error 3048 Cannot open any more databases

Jim Lawrence accessd at shaw.ca
Wed Mar 5 10:50:47 CST 2008


Hi Max:

Most ADO recordsets are not tied to the database where the data is
retrieved. That is not a disconnected recordset just one that is not
dynamically linked. To dynamically connect a ADO recordset to the database
would have use something like:

' By using the operand adOpenDynamic the recordset
' below remains linked to the dataset this process has retrieved
' and if any data changes take place during the use this recordset 
' those changes are dynamically reflected in recordset.
 
rsMyRecordset.Open objCmd, , adOpenDynamic, adLockOptimistic  

' Below is the most likely code to retrieve data for something like a 
' combo box object which does not require a dynamic link. The 
' connection to the database is terminated immediately after 
' the data has been retrieved.

rsMyRecordset.Open objCmd, , adOpenStatic, adLockOptimistic

HTH
Jim 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 05, 2008 5:33 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

Max,

To be honest I am not the one to be answering these questions since none of
my clients have required this feature, and in fact several of my clients are
still running access 2000 or a mix with 2000 included which disallows
general use of this feature - Access 2000 can't bind a form to an EDITABLE
ADO recordset.

It would be interesting to discover whether using ADO would speed up a
database application as the number of users rise.  If you think about it,
Access has to go through all of the connections discovering whether they
might need to update data.

At one client, I work "offline" much of the time, mapping local directories
to a drive letter for testing, then mapping network shares to that same
drive letter for live access to data.  When I am offline I get immediate
opening of very complex forms.  When working live it can require seconds to
open the same form.  If there are a hundred active connections per user
(combos, lists, forms, subforms etc), times 40 users that is a lot of
connections for Jet to wade through.  Imagine just making all of the combos
bound to disconnected ADO recordsets.  That would drop the active
connections at least by half, probably more like 80%.

I have a billing database here at my office that I will spend some time this
morning playing with this stuff and see what I see.  However I believe there
are others on the list who already do this and could help us figure out how
it is done.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, March 05, 2008 8:00 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

Well, I never knew that.  Learn something every day.

Do you have to tell ADO to disconnect or does it do it automatically.  If
auto, then do you assume it is closed in all cases and re-open it to do
something with it or do you test it (somehow) to see if it is disconnected?

Thanks
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 05, 2008 12:15 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

Max,

When you run a normal query in a combo or list or form etc. that is handled
by JET as a normal DAO recordset.  There is a fixed number of such
recordsets available to your application, the number being up somewhere
around 2000, which sounds like a lot but...

At any rate, in later versions of Access, access allows you to manually
create ADO recordsets and bind things like combos, lists and forms to those
recordsets.  ADO can disconnect once it has pulled the data, whereas DAO (to
my knowledge) does not have that capability.  IOW ADO can actually close its
connection to the database after pulling its data.

For combos this is not an issue, but for forms it creates issues where you
have to make sure that the data that you pulled from has not changed if you
are going to write your own changes back. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, March 05, 2008 4:33 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

John,
What do you mean by "disconnected" recordsets?
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 05, 2008 3:20 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

Yup!

If you are using Access XP or above, look at binding all combos and lists to
disconnected ADO recordsets.  That will help immensely all by itself.  After
that you will probably need to dive into the dark arts of ADO disconnected
recordsets for the form as well.  As Charlotte said, WANS are considered a
no-no for bound forms in Access.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Tuesday, March 04, 2008 8:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Error 3048 Cannot open any more databases

Darryl,

This is a highly misleading message, because it really means you have too
many connections open.  If you are doing things like populating combo boxes
with select statements, each one of them is opening a connection.  As I
recall, lookups in your tables could do that as well.
Access was NOT meant for WAN use, and to make it even marginally reliable in
that context, you have to rewrite the UI to eliminate any unnecessary
connections to the back end and use a JIT approach to populating lists,
forms, subforms, etc.  There have been previous discussions on this in the
list, so you might take a stroll through the archives and see what you find.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Tuesday, March 04, 2008 4:23 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Error 3048 Cannot open any more databases



Hey folks,

Sorry to bug you with this, Having a lot of, ummm, 'fun' with this database
I am building. Stand alone, it works great, with 5 or so users it works
great, over a WAN with multi users it gets very wobbly and corrupts easily.


Decided that it has to be split into FE and BE, which I have done manually
and linked up the tables. When the DB opens it populates a listbox with
choices for the user as before fine.  However when the users selects an
option which opens the data-entry form (and associated
subforms) I get this lovely error

"Error 3048 Cannot open any more databases"

on this line
sSQL = "SELECT * FROM tbl_FYP_APLID WHERE (((FYPID)=" & lFYPID & ") AND
((tbl_FYP_APLID.APLID)=" & lAPLID & "));"
[Forms]![frm_FYP_APLID].RecordSource = sSQL

Now I know clearly that I must have too "many databases open", but exactly
what the hell does that mean? how did it happen and how can I either 1:
increase the amount of DB's I can have open, or close down some of the
offending databases?

The only clue I have found online is if you don't SET your delcared
CurrentDB to NOTHING when you are done with it, But the code in this
workbook doesn't work like that at all and the modules that do use SET
current DB, does set them to nothing when finished.  Most confusing.

Can anyone shed some light on this. Sorry about all the questions - working
under lot of pressure and learning on the fly.  Educational for sure, but I
am getting through more and more scotch in the evening!!

:) Darryl.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list