Should close backend database after every SELECT/INSERT/UPDATE OR can leave it open

[ria.arora]

I want to check if we should close backend database after every SELECT / INSERT / UPDATE OR can leave it open and should close when user close the application.

I'm checking if all databases are available using below code. If any of database is not available it'll prompt the user.

    If SecuredDB.ConnectSecuredDatabase() = False Then
        iOk = MsgBox("Unable to open Secured Database", vbCritical, "Secured Database not found")
        cmdLogin.Visible = False
        Exit Sub
    End If

Function is used to connect local ACCESS Database and extract database information from local database and assign True value to global variable
Public Function ConnectSecuredDatabase() As Boolean
    Dim sDBNAME As String
    Dim sDBPassword As String
    
    On Error GoTo ErrConnectDB
    ConnectSecuredDatabase = False
    'Global Vairable "gsAccessSecDBPath" will contain the Secured MS Access Database file name
    sDBNAME = GetDBPath() & "\" & GetDBName()
    sDBPassword = GetDBPassword()
    
    'The DBEngine property of the Application object represents the Microsoft Jet database engine.
    'The DBEngine object is the top-level object in the Data Access Objects (DAO) model and it contains
    'and controls all other objects in the hierarchy of Data Access Objects.
    
    'Use the Workspace object to manage the current session or to start an additional session.
    'When you first refer to or use a Workspace object, you automatically create the default workspace, DBEngine.Workspaces(0).
    ' Create Microsoft Jet Workspace object.
    Set gCurWS = DBEngine.Workspaces(0)
    Set gsSecuredDatabase = gCurWS.OpenDatabase(sDBNAME, False, False, "MS Access;PWD=" & sDBPassword)
    ConnectSecuredDatabase = True
    gsSecuredDatabaseOpened = True  'Assign TRUE once MS Access Secured Database is Initialised
    
    Exit Function
ErrConnectDB:
    gsDBErr = Err.Description
End Function

'Disconnect Secured Access Database
Public Sub DisconnectSecuredDatabase()
    If gsSecuredDatabaseOpened = True Then  'If MS Access Secured Database is opened
        gsSecuredDatabase.Close
        gsSecuredDatabaseOpened = False
    End If
    Set gsSecuredDatabase = Nothing
End Sub

 

[RainLover]

I can't see why you are making the connection in the first place, let alone disconnect it.

If your Front End has Linked Tables the connection is always there while the Front End is Open.

Or am I not understanding your question.

 

[mdlueck]

I want to check if we should close backend database after every SELECT / INSERT / UPDATE OR can leave it open and should close when user close the application.

I do most of my DB work with ADO type objects. At the startup of the application I create the ADODB.Connection object and it stays in existence the entire time the application is running.

For each SELECT / INSERT / UPDATE I create a new adoCMD / adoRS object and destroy those as soon as it is done being used for its one SQL statement.

I also do about the same thing with DAO.QueryDef objects... those are useful to download records into FE temp tables. Those I clean up as soon as the query finishes. I leave no DAO.QueryDef objects statically defined in the database.

Finally I have a utility which cleans up the FE DB before I prepare it for production deployment:

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

 

[ria.arora]

This is not linked table. I checked while login time if all the source databases are available, if any of the database is not available prompt the user before starting any process.

 

[RainLover]

I can't see an advantage in not linking the Tables.

Why are you against this.