Cannot Open Anymore Database :-Error 3048

[paulcherianc]

Dear Experts!!!

I am developing an employee database and when i split it while running a VBA code it gives me the subject error It's highlighting the append query statement.

It was working before i split it. Can anyone help me. Is there any endless loop in my coding. Please help!

Private Sub Command308_Click()

 Dim Ret As String, userInputValue As Integer
 Ret = InputBox("Please enter the Password to Continue", "HRPro+")
    
    If Ret = "" Then
        MsgBox ("Action Cancelled!"), vbCritical, "HRPro+"
    Else
        If Ret = "Admin" Then

Dim rs As DAO.Recordset
Dim var As String
var = Me.EmpBadgeID
Forms![1AABEmployee_MasteR_Tab].RecordsetType = 0
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "EmpBadgeID = '" & var & "'"
Me.Bookmark = rs.Bookmark
Set rs = Nothing
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "History Table Individual Update"
    
Me.[SubFormContainer].Locked = False
    
    MsgBox "Current Details Exported to History Table. If you haven't made any profile changes kindly delete the history entry. Save after making any changes!", vbApplicationModal, "HRPro+"
      DoCmd.SetWarnings True
        
         Else
            MsgBox ("Incorrect Password. Try Again!"), vbCritical, "HRPro+"
        End If
        
    End If
    

End Sub

Thanks in Advance!

 

[paulcherianc]

:banghead:

 

[CJ_London]

is your query a union query? if so, how many unions?

What happens if you pause the code on the setwarning line, then open the query manually?

Through other commands, do you have other queries open?

What is the backend - access? sql server?

see this link for other suggestions

http://www.access-programmers.co.uk/forums/showthread.php?t=122973

 

[The_Doc_Man]

There is a thing you can do involving the DBEngine.Idle method (please look this up to get the syntax right) that MIGHT help if the problem is that you leave connections open.

However, given that Access allows a LOT of databases to be open at once, I'm wondering if your problem is that some queries are still open via recordsets that are opened on demand but not closed when you are done with them.

Also, are your multiple databases all Access .MDB/.ACCDB (or ...DE) files? Or do you have links to spreadsheets? Or are the backends some sort of SQL services (including SQL Server, ORACLE, MySQL, and other active backends)?

My concern is that you should not have this kind of problem unless something is VERY wrong with your logic or your design.

Set the breakpoint just before the offending line of code.

Now, from that code page, open up the "Locals" window. If you have never played with that before, it is "tree-diagram" oriented. You find the thing you want and can expand a selected branch to see its components.

It might take a bit of "diddling" to find it, but what you want to see is from your DBEngine, an object that you will have only one of. You want to find how many workspaces you have open by finding the Workspaces collection. Most likely, you will have one and only one. From that workspace, you want to see how many databases you have open by expanding the Databases collection item. There is also a workspace property you can examine called "Connections" that will tell you how many connections you have open.

From the "Immediate Window" you can also try this:

Debug.Print DBEngine(0).Workspaces(0).Connections

Debug.Print DBEngine(0).Workspaces(0).Databases.Count

Debug.Print DBEngine(0).Workspaces(0).Database(0).Name

Debug.Print DBEngine(0).Workspaces(0).Database(1).Name

...

(to see the count and all the names of databases that are open.) Note that the number is 0-based, so if your count says you have 3 databases, they are 0, 1, and 2.

The more interesting issue is whether those databases stay open even after this form or macro (in the latter case doing a RunCode?) closes. That would require you to have another form open, perhaps like a switchboard form but other forms would work - as long as you can get them to a break-point state so you can issue the commands to do the examination. I.e. the setup isn't trivial, but you can get a LOT of data if you get to that point.

 

[arnelgp]

can you show the update sql of this query: "History Table Individual Update"