Slow database: Persistent connection OpenDatabase method

[DajTwo]

I have an access 2003 front end back end setup used by 4-5 users.

Each user has a front end on their desktop and the back end resides in the commany's shared drive with a folder dedicated to the back end.

My problem is that if more than 1 user is using the dtabase, the forms can take more than 10 sec to load, and if on a VPN, more than 30 secs.

I am going through the top 100 performance top listed on teh FMS site however I need help to have a persistent connection. I have looked into this briefly with thread705-1522474 and found the following code again on the FMS site.

In order to implement this (and this is the question), I have to :

1- Insert a new module
2- Amend the code below with the location of the back end
3- Save the module as 'PersistentDB'

How do I 'call' this module from my opening form? Can I just enter OpenAllDatabases True?

Thanks


Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open _
' during the entire time the application runs.
' In: pfInit TRUE to initialize
' (call when application starts)
' FALSE to close
' (call when application ends)
' Created : FMS, Inc.

Dim x As Integer
Dim strName As String
Dim strMsg As String

' Maximum number of back end databases to link
Const cintMaxDatabases As Integer = 2

' List of databases kept in a static array
' so we can close them later
Static dbsOpen() As DAO.Database

If pfInit Then
ReDim dbsOpen(1 To cintMaxDatabases)
For x = 1 To cintMaxDatabases
' Specify your back end databases
Select Case x
Case 1:
strName = "H:\Dir\Backend1.mdb"
Case 2:
strName = "H:\Dir\Backend2.mdb"
End Select
strMsg = ""

On Error Resume Next
Set dbsOpen(x) = OpenDatabase(strName)
If Err.Number > 0 Then
strMsg = "Trouble opening database: " & strName & _
vbCrLf & _
"Make sure the drive is available." & _
vbCrLf & _
"Error: " & Err.Description & _
" (" & Err.Number & ")"
End If

On Error GoTo 0
If strMsg <> "" Then
MsgBox strMsg
Exit For
End If
Next x
Else
On Error Resume Next
For x = 1 To cintMaxDatabases
dbsOpen(x).Close
Next x
End If
End Sub



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

I'd say simply Yes to all the questions.
What is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks for the reply

These are my questions

How do I 'call' this module from my opening form?

On Load, enter OpenAllDatabases True ?

And on all exits, enter OpenAlldatabase False?

That is it?

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

I'd use an hidden starting form always open.
Load event procedure:
OpenAllDatabases True

Close event procedure:
OpenAllDatabases False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

The form has to remain always open but hidden?

When the user exit the database, then I I do a docmd close that form

Right?

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

No, the form will automatically close and fire the event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

I see, the user exits the database thus that action closes the form. Ok thanks

I am trying to hide the form with
Me.Visible = False

However the form remains visible. I could not see anything else that would completely hide the form (and not minimize it).

I also looked in the forum but could not find an example on how to hide a form..

Can you give me a pointer?

Thanks

If I did not say it before, I really appreciate the time and expertise of the users on this forum.