How to run a DAO OpenDatabase procedure???

[rjonas]

I have a database that I have split into a backend (tables) and frontend (all the other stuff). The backend DB resides on a network fileshare/server and the performance of editing queries and opening tables is less than desirable...

I found this procedure online that says by keeping the backend database open (persistent connection), it will dramatically increase the frontend DB performance.

Here is the text and procedure. My question pertains to the top part where it says "call the procedure" on the application start... How exactly??? I've tried tagging the procedure on the "OnOpen" command of the Main Menu form but that doesn't work or I've got the syntax wrong...

Any advice?? Much appreciated for any sage suggestions...

The DAO OpenDatabase Method

To create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method. Keep this variable open as long as your application is running.
The procedure below supports multiple backend databases. Edit the section with the list of databases to open, then call this when your application starts:

OpenAllDatabases True​

When you finish, call this to close the database variables/handles:

OpenAllDatabases False​

Here's the procedure code:
Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)


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:\folder\Backend1.mdb"
Case 2:
strName = "H:\folder\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

 

[HiTechCoach]

My question pertains to the top part where it says "call the procedure" on the application start... How exactly??? I've tried tagging the procedure on the "OnOpen" command of the Main Menu form but that doesn't work or I've got the syntax wrong...

Any advice?? Much appreciated for any sage suggestions...

How do you know it does not work?

Do you get any error message? Is yes, then what is the message.?

Based on the code you posted you are trying to open these databases:

"H:\folder\Backend1.mdb"
"H:\folder\Backend2.mdb"

Do you check to see if these files were created:

H:\folder\Backend1.ldb
H:\folder\Backend2.ldb


The code you posted is trying to open two database (see below).

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

Is that correct?

The code you posted is looking for these two database:

        Case 1:
          strName = "H:\folder\Backend1.mdb"  ' <<<<<<< Your first DB
        Case 2:
          strName = "H:\folder\Backend2.mdb"  ' <<<<<<< Your Second DB

Is this the correct paths to your two databases?

 

[boblarson]

By the way, you should not delete a post, especially if someone has answered in the thread. You should, in order to potentially help others in the future, post the solution you wound up with.

 

[HiTechCoach]

By the way, you should not delete a post, especially if someone has answered in the thread. You should, in order to potentially help others in the future, post the solution you wound up with.

I totally agree!

 

[Icewolf0927]

Hi
Do I enter the following code as a module? Then on a form do I just call this module then?
Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)


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:\folder\Backend1.mdb"
Case 2:
strName = "H:\folder\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

 

[HiTechCoach]

Yes you would place the code in a standard module and edit the section for the location of y9our back ends.

Here is the actual instructions on how to call it:

The DAO OpenDatabase Method

To create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method. Keep this variable open as long as your application is running.

The procedure below supports multiple backend databases.

Edit the section with the list of databases to open, then call this when your application starts:

OpenAllDatabases True

When you finish, call this to close the database variables/handles:

OpenAllDatabases False

 

[Icewolf0927]

Hi TechCoach

This is where I am getting lost. What does that mean and how would you do that? Does that mean I create two modules one for open and one for close name them those name OpenDatabases True and False. Then on the form that is what I put on the open and close?

 

[HiTechCoach]

One module.

You call the same sub OpenAllDatabases passing it a parameter.

 

[Icewolf0927]

I am sorry. I guess I am not getting where you are passing the parameter from. So do I put that parameter on the form on when it opens that the OpenAllDatabases is true. Then when it closes that it is false?

 

[HiTechCoach]

The parameter follows the sub name.

OpenAllDatabases True

Where True is the parameter passed the the sub named OpenAllDatabases

 

[Icewolf0927]

Thanks for taking your time to help me through this. So where do you put the false at the end before the sub ends? Then how does this module get called. Does it happen when the database opens automaticly or do you need to put this some where?

 

[HiTechCoach]

Thanks for taking your time to help me through this. So where do you put the false at the end before the sub ends? Then how does this module get called. Does it happen when the database opens automaticly or do you need to put this some where?

It will not be called automaticvally unless you use an autoexec macro or some other stratup routine.

Since I have not seen your dataabse I do not have any suggestion on where it would be best in your database to call this. You will need to do some testing.

 

[Icewolf0927]

How would I code it on the Autoexec? When I use open module it just opens it up in the VB view.

 

[HiTechCoach]

How would I code it on the Autoexec? When I use open module it just opens it up in the VB view.

To execute the code from the autoexec macro you will need to call a function.

 

[jose.natali]

I find this routine intresting for a project that I'm working with. I modify the routine a little bit, to make it "flexible" to my needs.

I will start the routine at login screen using the OnOpen event, and I will terminate the routine at the logout option of my project.

Here is the modify version:

Option Compare Database
Option Explicit
 
Public Sub OpenPersistentConnection(ByVal bEstablish As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : bStablish TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)
 
On Error GoTo Error_Handler
 
  Dim iLoop, iMaxConnection      As Integer
  Dim sName()                    As String
  Static dbsOpen()               As DAO.Database ' List of databases kept in a static array so we can close them later
 
  getConnections sName() ' get all backend databases from a local table
  iMaxConnection = UBound(sName) ' Count how many connections we have
 
  If bEstablish Then ' Create Persistent Connection
 
    ReDim dbsOpen(1 To iMaxConnection)
 
    For iLoop = 1 To iMaxConnection
      Set dbsOpen(iLoop) = OpenDatabase(sName(iLoop))
    Next iLoop
 
  Else  'Close all Persistent Connection
 
    For iLoop = 1 To iMaxConnection
      dbsOpen(iLoop).Close
      Set dbsOpen(iLoop) = Nothing
    Next iLoop
  End If
 
Exit_Routine:
  Erase sName, dbsOpen
  Exit Sub
 
Error_Handler:
  Select Case Err.number
    Case 91 'Object variable or With block variable not set
      'try to close connections the wasn't open
      Resume Exit_Routine
    Case 3024 ' Could not find file 'J:\MainDb\Data\FileName.accdb'.
      Resume Next
    Case Else
      MsgBox "An unexpected error has ocurred!" & vbNewLine & Err.number & ":" & vbNewLine & Err.Description & _
             vbNewLine & "If this problem persist, please contact your DBA!", vbCritical + vbOKOnly, "CRITICAL ERROR"
      'try to continue
      Resume Next
  End Select
 
End Sub
 
Private Sub getConnections(ByRef sName() As String)
' Connections table format:
' Field(0) = ID,            AutoNumber,  Table Key
' Field(1) = beName,        Text,        Back End Db Name      i.e. "DBname.mdb"
' Field(2) = belocation,    Text,        Back End Db Location  i.e. "J:\MainDb\Data\"
' Field(3) = beDescription, Text,        Back End Description
'
 
  Dim MyRS       As DAO.Recordset
  Dim iLoop      As Integer
  iLoop = DCount("*", "Connections")
 
  ReDim sName(1 To iLoop)
  Set MyRS = CurrentDb.OpenRecordset("Connections", dbOpenSnapshot)
 
  iLoop = 1
 
  While Not MyRS.EOF
    sName(iLoop) = MyRS.fields(2).value & MyRS.fields(1).value
    iLoop = iLoop + 1
    MyRS.MoveNext
  Wend
 
  MyRS.Close
  Set MyRS = Nothing
 
End Sub

 

[HiTechCoach]

Thanks for sharing.

FWIW, I only create Access applications that are split. I use this code in every application that has a Access (JET/ACE) backend(s).