Thanks a lot RuralGuy,
I tried to insert the following codes into the form. And then in On Open event, I entered "OpenAllDatabases", however, when the form is opened, error message pop up saying that Access cannot find the object. Not sure which part I am missing. Appreciate for advice. Thanks again.
Code:
Private Sub Form_Load()
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)
' From : 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 = "\\Sza-server-02\Work_Documents\Database\_DB_Sales\Bona_Sales_Record.accdb"
Case 2:
strName = "\\Sza-server-02\Work_Documents\Database\_DB_Sales\Product_Information_v4_SZ.accdb"
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