Focus between 2 instances of MS Access

[jonathanchye]

I have a switchboard ACCDE file which in turn opens another database (front end) on users machine.

It currently sort of works but I have problems when the database needs an update. I am using bob larson's updateFE code to update my front ends. I suspect the problem is with the focus of which is the active instance. I've ran some tests and the updateFE failed when the switchboard file closes. Sometimes I also have problems with windows not being visible... The front ends themselves open fine (even when running an update) if opened directly but not when opened from the Switchboard.

Below is my switchboard code to load the front ends. Hopefully someone can point out to be what is wrong. I am aiming to focus on the switchboard before moving onto the front ends and their UpdateFE code...

*I've added a 1.8 seconds pause before closing the Switchboard which seems to help a bit but I am sure there's a better way...

Public Function OpenDB(dbName As String, folderName As String)
On Error GoTo Err_Handler
Debug.Print "Starting OpenDB at " & Now() & "------"
Set WSHShell = CreateObject("WScript.Shell")
Dim DesktopPath, DocumentPath As String
DesktopPath = WSHShell.specialFolders("Desktop")
DocumentPath = fGetSpecialFolderLocation(CSIDL_PERSONAL)
Set fs = CreateObject("Scripting.filesystemObject")
'First of all check server this is db exists!
If FileExists("S:\Access Databases\" & folderName & "\" & dbName & ".accde") = False Then
        MsgBox "The database " & dbName & " doesn't exist. Please contact your Administrator.", vbInformation, "Database missing"
        Exit Function
 
'first check if the folder/file exist
ElseIf FolderExists(DocumentPath & "\Access DB (Do Not Delete)") = False Then
 
        Debug.Print "No folder - making folder"
        'No folder so create it on user desktop
        MkDir (DocumentPath & "\Access DB (Do Not Delete)")
        Debug.Print "copy file over after make folder"
        'copy the select DB over
        fs.copyfile "S:\Access Databases\" & folderName & "\" & dbName & ".accde", DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde"
'        'Pause to give time for the copy process to complete
        Pause (0.2)
        'Open the DB
        Dim objAccess As Access.Application
        Set objAccess = CreateObject("Access.application")
        DoCmd.RunCommand acCmdAppMinimize
        Debug.Print "Opening the DB"
                With objAccess
                        .OpenCurrentDatabase (DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde")
                        .Visible = True
                        .RunCommand acCmdAppRestore
                        .RunCommand acCmdAppMaximize
                End With
        Set objAccess = Nothing
        'Debug.Print "Shutting down Switchboard"
                Set WSHShell = Nothing
                Set fs = Nothing
                'wait in case there's an update...
                Pause (1.8)
                DoCmd.Quit acQuitSaveAll
Else
        'Folder exists
        Debug.Print "Folder exist"       
 
        'check if file exist
        If FileExists(DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde") = True Then
                Debug.Print "Folder and file already exist - just open it"
                'Folder and file already exist - just open it
                Set objAccess = CreateObject("Access.application")
                DoCmd.RunCommand acCmdAppMinimize
                Debug.Print "Opening the DB"
                With objAccess
                        .OpenCurrentDatabase (DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde")
'                         .Visible = True
                        .RunCommand acCmdAppRestore
                        .RunCommand acCmdAppMaximize
                End With
                Set WSHShell = Nothing
                Set fs = Nothing
                'wait in case there's an update...
                Pause (1.8)
                DoCmd.Quit acQuitSaveAll
        Else
 
                'Folder exist but file doesn't exist - copy it
                Debug.Print "Folder exist but file doesn't exist - copy it"
                fs.copyfile "S:\Access Databases\" & folderName & "\" & dbName & ".accde", DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde"
                Pause (0.2)
                Set objAccess = CreateObject("Access.application")
                DoCmd.RunCommand acCmdAppMinimize
                Debug.Print "Opening the DB"
                With objAccess
                        .OpenCurrentDatabase (DocumentPath & "\Access DB (Do Not Delete)\" & dbName & ".accde")
                       .Visible = True
                        .RunCommand acCmdAppRestore
                        .RunCommand acCmdAppMaximize
                End With
                Set objAccess = Nothing
                'Debug.Print "Shutting down Switchboard"
                Set WSHShell = Nothing
                Set fs = Nothing
                'wait in case there's an update...
                Pause (1.8)
                DoCmd.Quit acQuitSaveAll
        End If
End If
Debug.Print "End-----------------------------------------"
Set WSHShell = Nothing
Set fs = Nothing
Err_Handler:
If Err.Number = 0 Or Err.Number = 2501 Then
        Exit Function
Else
        MsgBox "Error : " & Err.Number & " - " & Err.Description, vbInformation, "Error in function OpenDB"
        Set WSHShell = Nothing
        Set fs = Nothing
        Set objAccess = Nothing
        DoCmd.Quit acQuitSaveAll
End If
End Function