Keeping linked database open

  • Thread starter Thread starter Lester Lane
  • Start date Start date
L

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....
 
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....
Click to expand...

Is this too simple or boring for everyone?!
 
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.
 
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.
Click to expand...

Thanks. It was easy then!
 
Back
Top