Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    56

    Unanswered: Error 3048 - Too Many DBs Open

    I am trying to narrow down where the error is occuring. I have made sure that all my rs's and db's are closed and set to nothing but I still seem to get this message. What puzzles me is that the error occurs when I only have one simple form open and one hidden form. I am using Sharepoint List as the backend and the hidden form maintains connection to the sharepoint site and then refreshes the webpage before the connection times out. I think I may have a loop or something that is causing the error. Below is the code I am using. The hidden form calls StartConnection() on load and ReconnectSPList() on timer.

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function StartConnection()
    'function that initializes connection to SP then checks if the current local version is correct
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strLocalVersion As String
        Dim strVersionSQL As String
        Dim strOnlineVersion As String
        
        Call ReconnectSPLists 'initialize connection
              
        strLocalVersion = "10.12.12" 'change this value before upload
        strOnlineVersion = "00.00.00"
        
        strVersionSQL = "SELECT [Equipment Manager].[Title], [Equipment Manager].[Online Version] FROM [Equipment Manager];"
        
        Set db = DBEngine(0)(0)
        Set rs = db.OpenRecordset(strVersionSQL)
        If rs.RecordCount = 0 Then
            strOnlineVersion = "00.00.00"
            Else
            strOnlineVersion = rs(1)
        End If
        
        If strLocalVersion <> strOnlineVersion Then
            rs.Close
            MsgBox "Your version of Equipment Manager (" & strLocalVersion & ") is incorrect. Click OK to navigate to Equipment Site and download the latest version (" & strOnlineVersion & ")", vbOKOnly
            Application.FollowHyperlink "https://wminet.weeksmarine.com/Construction/equip/SitePages/Home.aspx", , False
            DoCmd.Quit
            Else
            rs.Close
            End If
            Set rs = Nothing
            Set db = Nothing
    ExitErrHandler:
        Exit Function
    
    ErrHandler:
        Call ErrorLog(Err.Description, Err.Number, "StartConnection")
        Resume ExitErrHandler
    End Function
    
    Public Function ReconnectSPLists()
    On Error GoTo ErrHandler
        Dim ie As Object
        Dim ienew As Object
        
        Set ie = GetIE("https://wminet.weeksmarine.com/Construction/equip")
        If Not ie Is Nothing Then
            'found ie and refresh
            While ie.busy
                DoEvents
            Wend
            While ie.ReadyState <> 4
                DoEvents
            Wend
            
            ie.Document.Forms("aspnetForm").submit
            
        Else
            'open new ie
            Set ienew = CreateObject("internetexplorer.application")
            ienew.visible = True
            ienew.navigate "https://wminet.weeksmarine.com/Construction/equip/SitePages/Home.aspx"
            While ienew.busy
                DoEvents
            Wend
            While ienew.ReadyState <> 4
                DoEvents
            Wend
            
        End If
        Set ie = Nothing
        Set ienew = Nothing
    
    ExitErrHandler:
        Exit Function
    
    ErrHandler:
        Call ErrorLog(Err.Description, Err.Number, "ReconnectSPLists")
        Resume ExitErrHandler
    End Function
    
    Public Function GetIE(sAddress As String) As Object
    
        Dim objShell As Object
        Dim objShellWindows As Object
        Dim o As Object
        Dim retVal As Object
        Dim sURL As String
    
        Set retVal = Nothing
        Set objShell = CreateObject("Shell.Application")
        Set objShellWindows = objShell.Windows
    
        'see if IE is already open
        For Each o In objShellWindows
            sURL = ""
            On Error Resume Next
            sURL = o.Document.Location
            On Error GoTo 0
            If sURL <> "" Then
                If sURL Like sAddress & "*" Then
                    Set retVal = o
                    Exit For
                End If
            End If
        Next o
        
        Set GetIE = retVal
        Set objShell = Nothing
        Set objShellWindows = Nothing
        Set o = Nothing
        
    
    End Function
    
    Public Function HideMessageBar()
        
        Dim Start
            Start = Timer
            Do While Timer < Start + 1
                DoEvents
            Loop
            DoCmd****nCommand acCmdHideMessageBar
            
    End Function

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You should keep the connection open in a Public object, not re-open it every time you use it. Access has a limited number of connection available, at least when you work with the DAO library (I suppose that's the same when working with the ADODB library, though). When you release a connection:
    Code:
    cnn.Close
    Set cnn = Nothing
    It returns to a "pool of connections" but this does not mean that it is immedialetly available for re-use. If you open new connections too frequently, faster than the system can recycle the already opened then closed ones, it's possible to exhaust the whole pool.

    This is very clearly explained in an old book that's unfortunately not available any more but is a true gold mine when you work with DAO for connecting to non-Access databases:
    Hitchhicker's Guide to Visual Basic & SQL Server (fifth edition)
    William R. Vaughn
    Microsoft Press, 1997
    ISBN 1-57231-567-9
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    56
    I don't quite understand what you mean by keep the connection open in a public object. Are you saying keep move this to its own public sub or function?
    Code:
    Dim db As DAO.Database
    Set db = DBEngine(0)(0)
    Are the 5th and 6th edition "Hitchhicker's Guide to Visual Basic & SQL Server" similar? Both are available on Amazon so I will pick one up.

  4. #4
    Join Date
    Aug 2011
    Posts
    56
    I should probably also explain a little more about why I need to call this routine. I am currently using Access 2007 with SharePoint 2010. I used Access's feature to import SharePoint lists. I noticed that if I opened the Access application and tried to open any data from the SharePoint linked tables I would get an error that it could not find the table. I figured Access would prompt me for a login to SharePoint if I tried to access them through the Access application. Well I was wrong so I figured I would just have the Access application launch the SharePoint site upon opening and users could log in then and leave the website open in the background. Well that worked for a while until I noticed that the SharePoint site security token from logging in would expire and the connection to the SharePoint lists would get lost and Access would say it can't find the table. So I looked all over for an answer and could not find anyone else in my situation. (Probably because most people have more IT control then I do) Eventually I noticed that if I refresh or maintained some activity (pressing a button) on the SharePoint website page, Access wouldn't loose the connection to the tables and the SharePoint site security token would be refreshed. Basically the ReconnectSPList() function, checks to see if the SharePoint site is still open in the browser (open new one if it isn't) and "clicks" a button.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's how I usually do it, although the public object is a DAO.QueryDef in this example:
    Code:
    Option Compare Database
    Option Explicit
    
    ' Public objects.
    '
    Public p_SQLConnector As Cls_Std_SQLConnector
    Public p_Messenger As Cls_Std_E_Messenger
    Public p_qdf As DAO.QueryDef
    Public Sub StartPublicObjects()
    
    ' Instanciate the public class p_Messenger.
    '
        Set p_Messenger = New Cls_Std_E_Messenger
    
    ' Instanciate the public class p_SQLConnector.
    '
        Set p_SQLConnector = New Cls_Std_SQLConnector
        
    ' Instanciate the public QueryDef to communicate with the SQL Server.
    '
        Set p_qdf = CurrentDb.CreateQueryDef("")
        p_qdf.Connect = p_SQLConnector.Connection
        
    End Sub
    The procedure StartPublicObjects() is called when the application starts.

    I don't know about the 6th edition of W. R. Vaughn's book. It should be improved and revised as compared to the 5th, I guess. Anyway, if you intend to use DAO intensively, the book is worth its price.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •