Error 3048

[Leo Seccia]

Hello everyone,

I'm new to the group so I'm very sorry if this has been asked before.

I have an Access (2000) DB split in backend and frontend. Sometimes while a
user is browsing the forms the error:
'cannot open any more databases [3048]' is given.

Does anyone know what causes this and whether there is a way to solve the
issue?

Thanks in advance for your time.

Leo

 

[Jeanette Cunningham]

Leo,

Things to check:

1. Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.

2. Many combos/list boxes. Since each one has its own RowSource, you can hit
the limit if you have lots of fields on your form that use combos or list
boxes.

3. A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

4. Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.

5. Many subforms on the same form and all loaded at once.

6. Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase

Make sure your database variable (db) is set to Nothing before exiting the
routine:
Private Sub YourSub()
Dim db As DAO.Database

Set db = CurrentDb()
'your code here

SubExit:
Set db = Nothing 'Set to Nothing before exit.
Exit Sub

SubErr:
MsgBox Err.Description
Resume SubExit
End Sub

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

The reason is that the WorkSpace can only have 256 Database objects (Access
uses 3-4 intances internally) and you can hit the limit fairly quickly.

8. A data structure where a table has a very large number of fields.


As far as I understand, error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048 is
triggered whenever there are no more available table IDs.


a) Do you have the same list boxes on multiple pages of the tab control? If
they are unbound (or bound to the same field in the same table), could you
cut these list boxes, and paste them directly onto the form itself? This way
they show through the tab control regardless of the page you have selected,
so the result is as if they were on multiple pages, but you have reduced the
number of list boxes.


b) If that is not feasible, are there several list boxes that have the same
RowSource? If so, you could solve the problem by using a call-back function
in their RowSourceType. These are fairly cryptic to wrap your head around,
but essentially they hold a static array of the values, and supply them out
of memory as each list box calls, so no database connections are needed.
There's an example of such a function in Method 2 of this article:
http://allenbrowne.com/ser-19.html


Jeanette Cunningham

Hello everyone,

I'm new to the group so I'm very sorry if this has been asked before.

I have an Access (2000) DB split in backend and frontend. Sometimes while
a user is browsing the forms the error:
'cannot open any more databases [3048]' is given.

Does anyone know what causes this and whether there is a way to solve the
issue?

Thanks in advance for your time.

Leo

 

[Leo Seccia]

Hello Jeanette,

Thank you for taking the time to answer my question so extensively. It is
really appreciated.
I will check each point carefully (I have the feeling that a mixture of the
points you've made is causing the problem...)and get back to the group if
I'm still in trouble.

Thanks again.

Leo

Leo,

Things to check:

1. Domain aggregate functions in queries. Access runs the function for
every record, and this uses them up very quickly.

2. Many combos/list boxes. Since each one has its own RowSource, you can
hit
the limit if you have lots of fields on your form that use combos or list
boxes.

3. A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

4. Many open forms/reports, with subforms/subreports, using many queries,
or
using code referring to their RecordsetClone.

5. Many subforms on the same form and all loaded at once.

6. Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase

Make sure your database variable (db) is set to Nothing before exiting the
routine:
Private Sub YourSub()
Dim db As DAO.Database

Set db = CurrentDb()
'your code here

SubExit:
Set db = Nothing 'Set to Nothing before exit.
Exit Sub

SubErr:
MsgBox Err.Description
Resume SubExit
End Sub

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

The reason is that the WorkSpace can only have 256 Database objects
(Access
uses 3-4 intances internally) and you can hit the limit fairly quickly.

8. A data structure where a table has a very large number of fields.


As far as I understand, error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum
of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048
is
triggered whenever there are no more available table IDs.


a) Do you have the same list boxes on multiple pages of the tab control?
If
they are unbound (or bound to the same field in the same table), could you
cut these list boxes, and paste them directly onto the form itself? This
way
they show through the tab control regardless of the page you have
selected,
so the result is as if they were on multiple pages, but you have reduced
the
number of list boxes.


b) If that is not feasible, are there several list boxes that have the
same
RowSource? If so, you could solve the problem by using a call-back
function
in their RowSourceType. These are fairly cryptic to wrap your head around,
but essentially they hold a static array of the values, and supply them
out
of memory as each list box calls, so no database connections are needed.
There's an example of such a function in Method 2 of this article:
http://allenbrowne.com/ser-19.html


Jeanette Cunningham

Hello everyone,

I'm new to the group so I'm very sorry if this has been asked before.

I have an Access (2000) DB split in backend and frontend. Sometimes while
a user is browsing the forms the error:
'cannot open any more databases [3048]' is given.

Does anyone know what causes this and whether there is a way to solve the
issue?

Thanks in advance for your time.

Leo

 

[Leo Seccia]

Hello again,
I'm still investigating this...
Is there any way that you can measure the number of open handles/connections
to the back-end database?

Regards,

Leo

Leo,

Things to check:

1. Domain aggregate functions in queries. Access runs the function for
every record, and this uses them up very quickly.

2. Many combos/list boxes. Since each one has its own RowSource, you can
hit
the limit if you have lots of fields on your form that use combos or list
boxes.

3. A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

4. Many open forms/reports, with subforms/subreports, using many queries,
or
using code referring to their RecordsetClone.

5. Many subforms on the same form and all loaded at once.

6. Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase

Make sure your database variable (db) is set to Nothing before exiting the
routine:
Private Sub YourSub()
Dim db As DAO.Database

Set db = CurrentDb()
'your code here

SubExit:
Set db = Nothing 'Set to Nothing before exit.
Exit Sub

SubErr:
MsgBox Err.Description
Resume SubExit
End Sub

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

The reason is that the WorkSpace can only have 256 Database objects
(Access
uses 3-4 intances internally) and you can hit the limit fairly quickly.

8. A data structure where a table has a very large number of fields.


As far as I understand, error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum
of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048
is
triggered whenever there are no more available table IDs.


a) Do you have the same list boxes on multiple pages of the tab control?
If
they are unbound (or bound to the same field in the same table), could you
cut these list boxes, and paste them directly onto the form itself? This
way
they show through the tab control regardless of the page you have
selected,
so the result is as if they were on multiple pages, but you have reduced
the
number of list boxes.


b) If that is not feasible, are there several list boxes that have the
same
RowSource? If so, you could solve the problem by using a call-back
function
in their RowSourceType. These are fairly cryptic to wrap your head around,
but essentially they hold a static array of the values, and supply them
out
of memory as each list box calls, so no database connections are needed.
There's an example of such a function in Method 2 of this article:
http://allenbrowne.com/ser-19.html


Jeanette Cunningham

Hello everyone,

I'm new to the group so I'm very sorry if this has been asked before.

I have an Access (2000) DB split in backend and frontend. Sometimes while
a user is browsing the forms the error:
'cannot open any more databases [3048]' is given.

Does anyone know what causes this and whether there is a way to solve the
issue?

Thanks in advance for your time.

Leo

 

[Pieter Wijnen]

Sure thing, several ways
JetRooster (Jet 4.0+) works
also I posess (ancient) code published in 'Access advisor' by Ken Getz & al
on how to "read" .ldb's

Pieter

Hello again,
I'm still investigating this...
Is there any way that you can measure the number of open
handles/connections to the back-end database?

Regards,

Leo

Leo,

Things to check:

1. Domain aggregate functions in queries. Access runs the function for
every record, and this uses them up very quickly.

2. Many combos/list boxes. Since each one has its own RowSource, you can
hit
the limit if you have lots of fields on your form that use combos or list
boxes.

3. A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

4. Many open forms/reports, with subforms/subreports, using many queries,
or
using code referring to their RecordsetClone.

5. Many subforms on the same form and all loaded at once.

6. Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase

Make sure your database variable (db) is set to Nothing before exiting
the routine:
Private Sub YourSub()
Dim db As DAO.Database

Set db = CurrentDb()
'your code here

SubExit:
Set db = Nothing 'Set to Nothing before exit.
Exit Sub

SubErr:
MsgBox Err.Description
Resume SubExit
End Sub

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

The reason is that the WorkSpace can only have 256 Database objects
(Access
uses 3-4 intances internally) and you can hit the limit fairly quickly.

8. A data structure where a table has a very large number of fields.


As far as I understand, error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum
of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048
is
triggered whenever there are no more available table IDs.


a) Do you have the same list boxes on multiple pages of the tab control?
If
they are unbound (or bound to the same field in the same table), could
you
cut these list boxes, and paste them directly onto the form itself? This
way
they show through the tab control regardless of the page you have
selected,
so the result is as if they were on multiple pages, but you have reduced
the
number of list boxes.


b) If that is not feasible, are there several list boxes that have the
same
RowSource? If so, you could solve the problem by using a call-back
function
in their RowSourceType. These are fairly cryptic to wrap your head
around,
but essentially they hold a static array of the values, and supply them
out
of memory as each list box calls, so no database connections are needed.
There's an example of such a function in Method 2 of this article:
http://allenbrowne.com/ser-19.html


Jeanette Cunningham

Hello everyone,

I'm new to the group so I'm very sorry if this has been asked before.

I have an Access (2000) DB split in backend and frontend. Sometimes
while a user is browsing the forms the error:
'cannot open any more databases [3048]' is given.

Does anyone know what causes this and whether there is a way to solve
the issue?

Thanks in advance for your time.

Leo