Error 3048 ("Can't open any more databases") 1

[ump38]

I have a multi-user Access (Version 7.0) database with FE/BE database files...actually three back-ends. One of the "back-end" files contains only 1 table as it is the most dynamic table in the system and with issues of file corruption it made sense to be able just to be able to backup this critically changing data. The other two back-end files are relatively static in their data content and actually aren't even updatable by all end-users except one. The FE and these two relatively static BEs are copied from the server to the user's workstation with the FE being linked appropriately to 1 table on the server with all others linked locally.

Now, my form processes all use unbound text box controls where data is extracted from (using dbOpenSnapshot) and saved to (using OpenDynaset) this 1 dynamic table using recordsets and VBA. I have verified that all recordset processing loops and OnError routines properly close the recordset as data is extracted or saved.

Today, when a single user edited 150-200 records she received error 3048(Can't open any more databases.) I could watch on the NT server as the lock file (.ldb) was constantly appearing, then disappearing as she saved record after record.

Any ideas on what to look for on this particular error? Does anyone know what the threshold is as far as open databases. I don't know how to interpret what this error is really trying to tell me. Is there any type of trappable error (other than the 3048) that I can use to determine how many open databases there are at any given point in time. In my mind... there should never be more than maybe a couple. Thanks in advance for any help you can offer.

-Ump38

 

[Steve101]

What would appear to be happening is that a new database is being opened for each record being edited (and not closed). This is clearly a fundamental bug in the code. If the code is not too long, why not publish it. That way we'll be able to see if we can identify the source of the problem; eg. you may be opening the database as part of the record processing loop instead of above the loop.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[ump38]

Steve,
Sorry for how long this code section might appear but if it helps in debugging this 3048 error then it's worth it. This is the code for "updating" a record.... there's a similar module for adding a new record with only difference being ".AddNew" instead of ".Edit" and the setting of the Primary Key field ("SSN_Sequence&quot. Similar to the two lines included in this code below for:

UpdateFingerprintTable.Close
Set UpdateFingerprintTable = Nothing

Should I be doing something like that for "DB.close"? Maybe that's what I'm missing???

Public Sub UpdateThisRecord(ThisForm As Form)
Dim UniqueID As String
Dim SQLCode As String
Dim DB As DATABASE
Dim FingerprintTable As Recordset
Dim attempts As Integer
Dim SavedErrorMsg As String

UniqueID=Chr(34) & ThisForm!txt_ThisSSN & "-" & ThisForm!txt_SequenceNbr & Chr(34)

SQLCode = "SELECT DISTINCTROW [tblCapturedFingerprints].* "
SQLCode = SQLCode & "FROM [tblCapturedFingerprints] "
SQLCode = SQLCode & "WHERE ((([tblCapturedFingerprints].SSN_Sequence)=" & UniqueID & "));"

Set DB = CurrentDb
attempts = 0
On Error GoTo UnableToEdit
Try_AgainToOpen:
Set FingerprintTable=DB.OpenRecordset(SQLCode, dbOpenDynaset)
FingerprintTable.MoveFirst

On Error GoTo UnableToLock
attempts = 0
TryAgainToLock:
FingerprintTable.LockEdits = True
With FingerprintTable
     .Edit
     ![FirstName] = Left(ThisForm!txt_FirstName, 30)
     ![MiddleName] = Left(ThisForm!txt_MiddleName, 30)
     ![LastName] = Left(ThisForm!txt_LastName, 30)
      
      .... 'several other fields saved but excluded in code sample for space considerations

     ![FingerprintedWhere] = Left(ThisForm!cbo_BaseStation, 3)
     .UPDATE
End With

FingerprintTable.Close
Set FingerprintTable = Nothing

Exit Sub

UnableToEdit:
    attempts = attempts + 1
    If attempts < 5 Then
        Call PauseAwhile(1)  ' causes a 1 second delay
        Resume Try_AgainToOpen
    End If
    SavedErrorMsg = &quot;Err number: &quot; & Err.Number & Chr(13) & Err.Description & Chr(13)
    SavedErrorMsg = SavedErrorMsg & &quot;ID:  &quot; & UniqueID
    Resume CouldNotSave

UnableToLock:
    attempts = attempts + 1
    If attempts < 5 Then
        Call PauseAwhile(1)  ' causes a 1 second delay
        Resume TryAgainToLock
    End If
    SavedErrorMsg = &quot;Err number: &quot; & Err.Number & Chr(13) & Err.Description & Chr(13)
    SavedErrorMsg = SavedErrorMsg & &quot;ID:  &quot; & UniqueID
    FingerprintTable.Close
    Set FingerprintTable = Nothing
    Resume CouldNotSave

CouldNotSave:
    Dim ErrMsg As String
    ErrMsg = ErrMsg & &quot;An internal error has occurred while trying to 'UPDATE' &quot; & Chr(13)
    ErrMsg = ErrMsg & &quot;this particular record (existing) in the CHRC database.&quot; & Chr(13)
    ErrMsg = ErrMsg & &quot;This record has NOT been saved.&quot; & Chr(13) & Chr(13)
    ErrMsg = ErrMsg & &quot;Please record this message and report this condition to your&quot; & Chr(13)
    ErrMsg = ErrMsg & &quot;database administrator if it persists.&quot; & Chr(13) & Chr(13)
    ErrMsg = ErrMsg & SavedErrorMsg
    MsgBox ErrMsg, vbCritical, &quot;Unable to save this record!&quot;
    Exit Sub

End Sub

Thanks!!!!!

 

[Steve101]

You absolutely should be including

DB.Close
SET DB = Nothing

lines of code.

Add these two lines after the

Set FingerprintTable = Nothing

Your error handling at the moment is a little "scattered". My immediate concern is that the way it is, the two lines that I've just suggested need adding may never be executed, because of the "Exit Sub" you have at the bottom of the posted code.

You will need to adapt your error handling code, so that once the database is successfully opened, the error handler does its thing, and then resumes to a label which causes the required recordset and database commands to be always closed if they were opened.

Hope this helps; Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[ump38]

Thanks Steve!
The code snippet I'd included earlier was not an exact cut&paste from my module but rather a simulation in general context that admittedly make it appear a bit more "scattered" than it really is.

The fact that I didn't have the:

DB.Close
SET DB = Nothing

will hopefully make all the difference in preventing this 3048 error.

 

[Diesels]

This error has kicked my butt for 4 months now. 66 users have this software and 1 user gets it when she adds more then 26 or 27 claims or deviations. Which may occure about 1 or 2 times a decade. However the *$%R insists that the error be corrected. I tried everything suggested here and everything suggested when searching on google for this error. Nothing worked. So my workaround was this. If they get the error, click on the a menu item that is named 3048 error. What I did was shut down the application and call it again.

Unload frmDeviations
If Forms.Count = 2 Then Unload frmClaims
If Forms.Count = 1 Then
Unload Me
gdbTenneco.Close
Set gdbTenneco = Nothing
Shell (gstrDBPath + "App.exe&quot, vbMaximizedFocus
End 'ends this program while the other is starting up

End If

Diesel

 

[AccessUser22]

This error is really starting to bother me. I get this error when I load my main data entry form and try to access one of the combo box drop downs. The data in the drop down isn't there and sometimes I'll get the 3048 error of cannot open any more databases. I'm totally lost on how to solve this and would appreciate any info that can be provided. I've slowly been solving this error by lightening my form and taking away things that don't necessarily need to be there. In the past that seemed to work but now I just repeatedly get the error for no reason. I can't see why I would need to lighten the form any more than it is.

Any ideas would be appreciated.