The DAO OpenDatabase Method

[tagteam]

I have found the code on how to do this by googling but I still cant seem to find the explanation on how to implement it

this is what is in my module caled OpenAll Databases

Option Compare Database




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)
  ' Source  : Total Visual SourceBook


  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 = "MyDatabasePathHere"
        Case 2:
          strName = "MyDatabasePathHere"
      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

The problem is I have tried several methods to get this to run and none of them work.

I have a form that opens when the database opens. I just can't figure out how to get this to run. I have tried the following:



1. in the on open event of the form type OpenAllDatabases = True
2. in the on open event of the form use Macro builder and use macro OpenVisualBasicModule. Then tried putting true in the procedure and tired OpenAllDatases = true on the first line but none of it worked.

What is the proper way to implement this

[June7]

Open event should work.

Should I presume that instead of "MyDatabasePathHere" you have the full folder\file path of your actual databases?

Put message boxes and/or Debug.Print at strategic locations in code to verify that it runs and variables populate as expected.

[tagteam]

Yes I have the full folder path of the actual database in the code.

When i type my module name OpenAllDatabases = True and I try to open the form I get the following Error:

Database cannot find the object 'OpenAllDatabases = True'
if 'OpenAllDatabases = True' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

So it is looking for a macro when I do that and i just have this code in a module. Should i put this in a macro first. I would put the debug stuff in but I dont think it is even getting to the point where it runs the code.

[June7]

Try:

OpenAllDatabases(True)

or

Call OpenAllDatabases(True)

Or eliminate the Sub and put all the code directly in the Open event (would have to modify a little to remove the pfInit condition).

[tagteam]

I get the same error as above when i try OpenAllDatabases(True)

what is the normal method for running vba code when you open a form?

[June7]

I use the Form Load and Open and Current events. Sometimes all the code is in the event, sometimes call a sub. Maybe the Load event will be better.

Is the sub in a general module or behind the form?

Another approach is AutoExec macro (I've never used it) to run code when database opens. Macros can call only VBA functions, not subs.

[tagteam]

The sub is in a general module. I went to Create -> module and entered my code there and gave it a name.

I have tried removing the Sub OpenAllDatabases(pfInit As Boolean) and End sub and just putting it in the Private Sub Form_Load() and in the Private Sub Form_close() and it does not give me any errors. However I cant tell if it is actually doing anything. It doesnt really seem any faster.

[June7]

If you didn't edit for the pfInit condition, then it likely does not do anything.

It would not be faster.

Use debug techniques (breakpoint, message box, Debug.Print).

Every module should have Option Explicit in the header. This will reveal variables being used but not declared.