Keeping linked database open

[Lester Lane]

I gather that performance is better if the database that holds your
linked tables is kept open in code whilst the app is running. However
there is no method I can find for testing if it is open.

I have [Global db As DAO.Database] in one of my modules so I can
always have this pre-set when I use code thoughout the system such as
db.OpenRecordset. Is this enough? Because I have used another Global
called dbsCurrent and I set this to the database with code in a splash
screen (which closes after 5 seconds), but I never reference it (this
just hangs around keeping the link open - I think:

Public Sub Form_Load()
On Error GoTo ErrTrap
Dim rsTemp As DAO.Recordset
Dim strSQL As String
Dim CurrentFile As String

Set db = CurrentDb() 'SET ONCE HERE!
strSQL = "SELECT DataPath FROM tblSystem"
Set rsTemp = db.OpenRecordset(strSQL)

If Not IsNull(rsTemp!DataPath) Then
strDataPath = rsTemp!DataPath
CurrentFile = strDataPath & "\Database_Data.mdb"
Set dbsCurrent = OpenDatabase(CurrentFile)
Else
MsgBox "Please go to the System screen and select the datafile
you are linked to."
End If

rsTemp.Close

Exit Sub

ErrTrap: etc

When I Debug.Print the db.Name or dbsCurrent.Name I get the path -
does this mean it is really open or is this a static value just set in
memory? Do I need both DAO.Database variables? Thanks....

 

[Lester Lane]

I gather that performance is better if the database that holds your
linked tables is kept open in code whilst the app is running.  However
there is no method I can find for testing if it is open.

I have [Global db As DAO.Database] in one of my modules so I can
always have this pre-set when I use code thoughout the system such as
db.OpenRecordset.  Is this enough?  Because I have used another Global
called dbsCurrent and I set this to the database with code in a splash
screen (which closes after 5 seconds), but I never reference it (this
just hangs around keeping the link open - I think:

Public Sub Form_Load()
On Error GoTo ErrTrap
    Dim rsTemp As DAO.Recordset
    Dim strSQL As String
    Dim CurrentFile As String

    Set db = CurrentDb() 'SET ONCE HERE!
    strSQL = "SELECT DataPath FROM tblSystem"
    Set rsTemp = db.OpenRecordset(strSQL)

    If Not IsNull(rsTemp!DataPath) Then
        strDataPath = rsTemp!DataPath
        CurrentFile = strDataPath & "\Database_Data.mdb"
        Set dbsCurrent = OpenDatabase(CurrentFile)
    Else
        MsgBox "Please go to the System screen and select the datafile
you are linked to."
    End If

    rsTemp.Close

Exit Sub

ErrTrap: etc

When I Debug.Print the db.Name or dbsCurrent.Name I get the path -
does this mean it is really open or is this a static value just set in
memory?  Do I need both DAO.Database variables? Thanks....

Is this too simple or boring for everyone?!

 

[Douglas J. Steele]

The easiest way to ensure that there's an open link to your database is to
have a form that displays data from the database open. The form does not
need to be visible.

 

[Lester Lane]

The easiest way to ensure that there's an open link to your database is to
have a form that displays data from the database open. The form does not
need to be visible.

Thanks. It was easy then!