Runtime error 3048 "Cannot open anymore databases"

[Fernando]

Hi there,

I've serached the net for information on this error and although there is a
lot of information about it, it doesn't seem to be an easy one. Probably
because it is not clear what is going on behind the scenes.

The code breaks after using an access app for a while, and it breaks in an
event on the line :
Set db = CurrentDb

As I understand it, the Set sets a reference (a pointer) to an object (a
Database object). Later in the code I have:
db.close

and to make sure the memory is freed I also have:
Set db = Nothing

Well, the problem persists. Access doesn't seem to be freeing the
references, so it reaches the maximun number of allowed table ids, and it
hungs.

Any help on this would be much appreciated.

Thank you very much in advance !

 

[Andrea M]

Fernando,

Is it possible that your code is stuck in a loop? I recently got this error
and it turns out that I had a continuous loop that maxed out the database and
recordset assignments.

Good luck,
Andrea

* Please click "Yes" if my answer has helped you. *

 

[Fernando]

Hi Andrea,

Thank you for your reply.

The code is not in a loop. It is in the form's dirty event handler. So every
time the forms data changes (it gets dirty), the dirty event gets fired and
its handler gets called. More specifically, the code lives inside an if block.

What I don't understand is that in the one single block of code I do Set db
= CurrentDb and then when I am done I do db.close and Set db = Nothing. So I
would expect the db and recordsets to be freed.

Thank you again !

 

[Andrea M]

Can you post the code?

 

[Fernando]

Hi Andrea, here is the code ..


Private Sub Form_Dirty(Cancel As Integer)
Dim strQuery As String
Dim TowerId, tmpTowerId As String
Dim TicketId, RefCount As Integer
Dim db As DAO.Database
Dim rcd As DAO.Recordset

' check if it is a new record or
TicketId = Me.TicketId.Value

If IsNull(TicketId) Then
'MsgBox "New Record"
' set the database
Set db = CurrentDb
Me.comboBoxTower.Value = "Unassigned"
' get the proper reference count for unassigned
strQuery = "select Count(*) As Reff from Ticket where TowerId ='" &
Me.comboBoxTower.Value & "'"

Set rcd = db.OpenRecordset(strQuery)
rcd.MoveFirst
VehicleCount = CInt(rcd!Reff) + 1
Me.txtTowerRef.Value = VehicleCount
rcd.Close
db.Close
Set rcd = Nothing
Set db = Nothing
End If



End Sub

 

[Van T. Dinh]

1. Try changing the statement:

Set db = CurrentDb

to

Set db = DBEngine(0)(0)

AND comment out the statement:

db.Close

2. Do you use the "Option Explicit"? I can't see you declared the variable
VehicleCount anywhere.

3. Are you aware that the dim statement

Dim TowerId, tmpTowerId As String

, only tmpTowerID is dim'ed as String and TowerID is dim'ed as Variant?

If you want to declare them as String, you need to use:

Dim TowerId As String, tmpTowerId As String

i.e. you need an AS Clause for every variable. The same problem occurs in
your other Dim statements.

 

[Van T. Dinh]

BTW, it seems to me that you are using the code to assign a sequential
number [TowerRef] per [TowerID]. In this case, it is much better to use the
Form_BeforeUpdate Event rather than Form_Dirty.

The problem with your method is that other users can try to add Records at
nearly the same time and you may end up with multiple Records with the same
[TowerRef] for the same [TowerID].

 

[Fernando]

Hi Van,

Thank you very much for your reply. All your suggestions and comments are
great and well received.

I would like to focus on your suggestion to change the Set db statement to
Set db = DBEngine(0)(0) and get rid of the db.close statement. I'd like to
focus on that because I believe this is what is causing me problems ....
presumably leaving dangling references and maxing the table id number so the
3048 error. What I would like to ask you is to expand on WHY do you think the
changes you suggest would solve my problem. In doing that I will learn the
WHY and I will know more than yesterday.

Also, I should point out that one of the difficulties I am having is that
although one user is getting the 3048 consistently every time - we can
reproduce it in his machine, I can't make it occur in any of my boxes. I am
saying this now because I am starting to suspect that this is an issue with
Access and it might be the case that it has been solved in any of the patches
to Access or to the OS.

Thank you very much,

 

[Van T. Dinh]

A WorkSpace can have 256 (?) databases open. However, Access itself uses a
number of instances and if you add too many instances, you will get the
error. I did a test once and it crashed out about 252.

AFAIK, CurrentDB actually creates another copy of the database object in the
WorkSpace while DBEngine(0)(0) simply refers to the database object Access
itself uses (DBEngine(0) refers to the current WorkSpace). There are slight
differences in how up-to-date the list of objects (not the data in the
objects) in each database. Generally, CurrentDB creates a new copy so it is
more up-to-date. This only applies in cases such as creating temporary
Tables, etc ... so quite often, you can use DBEngine(0)(0) in lieu of
CurrentDb(), which in theory, should be faster (but the difference is
minimal and in my experience, not noticeable).

I wrote "Try ..." in the previous post since there may be other processes
creating database objects. Let me say that there is nothing wrong with
using CurrentDB and my guess is something wrong with this particular Access
installation. If the user has a separate Front-End, do a Compact and Repair
on the Front-End. If that doesn't work, do a Repair on the Access / Office
software. If necessary, restore Office / Access.

Remember to apply Windows and Office / Access Service Releases / Service
Patches.

 

[Fernando]

Hi Van,

Thank you for your reply.

What you say makes sense. I can't see anything wrong with using CurrentDb.
More than that, I find it very strange that closing it and setting it to
nothing does't help either. Also, it only hapens in one machine (I can't
reproduce it) and it hapens consistently practicaly at the very beginning. So
it sounds that there is something wrong with the installation. Anyway I have
learned in the process. Thanks.

Cheers,
--
Fernando

A WorkSpace can have 256 (?) databases open. However, Access itself uses a
number of instances and if you add too many instances, you will get the
error. I did a test once and it crashed out about 252.

AFAIK, CurrentDB actually creates another copy of the database object in the
WorkSpace while DBEngine(0)(0) simply refers to the database object Access
itself uses (DBEngine(0) refers to the current WorkSpace). There are slight
differences in how up-to-date the list of objects (not the data in the
objects) in each database. Generally, CurrentDB creates a new copy so it is
more up-to-date. This only applies in cases such as creating temporary
Tables, etc ... so quite often, you can use DBEngine(0)(0) in lieu of
CurrentDb(), which in theory, should be faster (but the difference is
minimal and in my experience, not noticeable).

I wrote "Try ..." in the previous post since there may be other processes
creating database objects. Let me say that there is nothing wrong with
using CurrentDB and my guess is something wrong with this particular Access
installation. If the user has a separate Front-End, do a Compact and Repair
on the Front-End. If that doesn't work, do a Repair on the Access / Office
software. If necessary, restore Office / Access.

Remember to apply Windows and Office / Access Service Releases / Service
Patches.

 

[Fernando]

Van,

One last question. When you say 'do a Repair on the Access/Office software'
do you mean, in Access use the 'Help/Detect and Repair' menu item ? ... after
presumably checking for Updates.

Thanks
--
Fernando

A WorkSpace can have 256 (?) databases open. However, Access itself uses a
number of instances and if you add too many instances, you will get the
error. I did a test once and it crashed out about 252.

AFAIK, CurrentDB actually creates another copy of the database object in the
WorkSpace while DBEngine(0)(0) simply refers to the database object Access
itself uses (DBEngine(0) refers to the current WorkSpace). There are slight
differences in how up-to-date the list of objects (not the data in the
objects) in each database. Generally, CurrentDB creates a new copy so it is
more up-to-date. This only applies in cases such as creating temporary
Tables, etc ... so quite often, you can use DBEngine(0)(0) in lieu of
CurrentDb(), which in theory, should be faster (but the difference is
minimal and in my experience, not noticeable).

I wrote "Try ..." in the previous post since there may be other processes
creating database objects. Let me say that there is nothing wrong with
using CurrentDB and my guess is something wrong with this particular Access
installation. If the user has a separate Front-End, do a Compact and Repair
on the Front-End. If that doesn't work, do a Repair on the Access / Office
software. If necessary, restore Office / Access.

Remember to apply Windows and Office / Access Service Releases / Service
Patches.

 

[Van T. Dinh]

You're welcome ... Glad I could help ...

 

[Van T. Dinh]

Yep. I think you can also use Control Panel / Add-Remove Programs and the
"Office" entry in the list.