Database opening slowly - part 2

[mcelligott]

Hello All,

History: I have a database that opens very slowly. It takes easily between 45-60 seconds to open. I have stepped through the program and found exactly what command is causing the problem. The form "ECD Splash Screen" is designated as the form to display in the start up options. When the form loads, it confirms that the front-end is connected to the back-end tables. If it does not find the primary back-end, it looks for other appropriate back-ends. This part works extremely well.

It is when I get to the following statement that the delay actually occurs:

DoCmd.Close acForm, "ECD Splash Screen"

The switchboard will not display until the above command has completed. The delay is causing a real problem for us. We are a 24/7 emergency organization. The program needs to load faster.

Any thoughts what is going on and how to fix it?

Any help would be greatly appreciated.

Bob

 

[MajP]

What code is associated to the form's onclose event.

Out of curiosity. What happens if you change that line to

forms("ECD Splash Screen").visible = false

 

[PHV]

We are a 24/7 emergency organization
With an ms-access application ?
For such a critical app. I strongly suggest to use a real professional tool.

 

[mcelligott]

Hi MajP,

I do not have anything in the onclose event. The close statement I mentioned is located at the end of the programming that checks for the connection to the back-end. I have not thought of changing the visible setting to false before. I could do that. Wouldn't the form just sit in the background then?

Hi PHV,

I am hoping to move to SQL in the future. I initially worked with what I had and knew. Until I built the redundancy into the database, there was no delay issue.

Bob

 

[MajP]

If closing the splash screen truly causes the delay, and there is no code being run, then something else has to be going on.

I have never heard of slow form closing. My assumption is that the form's closing is causing another event to occur.

My first guess is that some procedure calls the splash screen to open in dialog mode. This causes execution of the calling code to halt. Once the splash screen closes/hidden code execution returns to the calling code.

If that is the case, is there code that calls the splash screen to open? Please post it.

Leaving a form open and hidden, is not such a big deal. Possibly (but unlikely) this form holds a persistent connection to the back-end which you should be doing anyways.

 

[mcelligott]

Hi again MajP,

I tried your first suggestion (setting visible to false). The form does not appear to have a visible setting.

The form opens automatically when the database opens. I have placed it in the StartUp option for the database. The following code is what runs when the form initially opens.

Private Sub Form_Open(Cancel As Integer)

Dim strServerPath As String 'path to server
Dim strAltServerPath As String 'path to alternate ECD server
Dim strVPNPath As String 'path to ECD server via VPN connection
Dim strMCUPath As String 'path to MCU server
Dim strLocalPath As String  'path to local workstation
Dim strGFDPath As String 'path to Gates FD
Dim strPerintonPath As String 'path to Perinton Ambulance
Dim FSO 'File System Object
Dim response As String

Set FSO = CreateObject("scripting.filesystemobject")

strServerPath = "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb"
strAltServerPath = "\\fd01map\alternate server\ECD Operations Database_be.mdb"
strVPNPath = "\\10.100.91.3\database$\operations database\ECD Operations Database_be.mdb"
strMCUPath = "\\abernas\operations database\ECD Operations Database_be.mdb"
strGFDPath = "\\Dispatch_2\operations\ECD Operations Database_be.mdb"
strPerintonPath = "o:\ecd\ECD Operations Database_be.mdb"
strLocalPath = "c:\operations\ECD Operations Database_be.mdb"

If FSO.FileExists(strServerPath) = True Then
   Call pfECDServerReLink
ElseIf FSO.FileExists(strMCUPath) = True Then
   Call pfMCUReLink
ElseIf FSO.FileExists(strGFDPath) = True Then
   Call pfGFDReLink
ElseIf FSO.FileExists(strAltServerPath) = True Then
   Call pfAltServerReLink
ElseIf FSO.FileExists(strVPNPath) = True Then
   Call pfVPNRelink
ElseIf FSO.FileExists(strPerintonPath) = True Then
   Call pfPerintonReLink
Else
   Call pfLocalReLink
End If

lastline:
End Sub
Function pfECDServerReLink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to the ECD Server
If firsttbl.Connect = ";Database=" & "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to the ECD server if not already connected
MsgBox "Reconnecting to the ECD Server, this will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
         Tdf.Connect = ";Database=" & "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb"
         Tdf.RefreshLink
    End If
Next

lastline:

DoCmd.OpenForm "Switchboard", acNormal
DoCmd.Maximize
DoCmd.Close acForm, "ECD Splash Screen"

End Function
Function pfAltServerReLink()
Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to the ECD Server
If firsttbl.Connect = ";Database=" & "\\fd01map\Operations\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to FD01 mapping computer if not already connected
MsgBox "The ECD Server is unavailable, connecting to the FD01 mapping computer.  This will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
        Tdf.Connect = ";Database=" & "\\fd01map\operations\ECD Operations Database_be.mdb"
        Tdf.RefreshLink
    End If
Next

lastline:
End Function
Function pfVPNRelink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to the ECD Server via VPN
If firsttbl.Connect = ";Database=" & "\\10.100.91.3\Database$\Operations Database\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to ECD via VPN if not already connected
MsgBox "Connecting to the ECD Server via VPN.  This will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
        Tdf.Connect = ";Database=" & "\\10.100.91.3\database$\operations database\ECD Operations Database_be.mdb"
        Tdf.RefreshLink
    End If
Next

lastline:
End Function
Function pfGFDReLink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
End Select

'Determines if the table Alarm Companies is already connected to the Gates FD
If firsttbl.Connect = ";Database=" & "\\Dispatch_2\Operations\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to the Gates FD if not already connected
MsgBox "Reconnecting to the Gates FD, this will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
         Tdf.Connect = ";Database=" & "\\Dispatch_2\Operations\ECD Operations Database_be.mdb"
         Tdf.RefreshLink
    End If
Next

lastline:
End Function
Function pfPerintonReLink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to Perinton
If firsttbl.Connect = ";Database=" & "o:\Ecd\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to Perinton if not already connected
MsgBox "Reconnecting to Perinton, this will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
         Tdf.Connect = ";Database=" & "o:\Ecd\ECD Operations Database_be.mdb"
         Tdf.RefreshLink
    End If
Next

lastline:
End Function
Function pfLocalReLink()
Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Loop through the tables collection
MsgBox "No networks were found, connecting to the your computer.  This data may not be as up-to-date.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
        Tdf.Connect = ";Database=" & "C:\Operations\ECD Operations Database_be.mdb"
        Tdf.RefreshLink
    End If
Next

lastline:
End Function
Function pfMCUReLink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to the MCU Server
If firsttbl.Connect = ";Database=" & "\\Abernas\Operations Database\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to MCU if not already connected
MsgBox "The ECD Server is unavailable, connecting to the MCU server.  This will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
        Tdf.Connect = ";Database=" & "\\Abernas\operations database\ECD Operations Database_be.mdb"
        Tdf.RefreshLink
    End If
Next

lastline:
End Function

The only thing I can think of is when the form attempts to close down, it is slowing down trying to unload the variables. What do you think?

Bob

 

[MajP]

The form has a visible setting. What suggests that it does not?

 

[mcelligott]

I looked at the form properties and there is no visible setting listed. I also tried just programming it into the VBA code (forms![ECD Splash Screen].visible = false) and it would not go away.

 

[MajP]

Where is this code called from
DoCmd.Close acForm, "ECD Splash Screen"

Put this at the end of the open event

DoEvents
MsgBox "Relink Complete"
lastline:
End Sub

Does the message box pop up in a timely manner? I am guessing that if you can not hide the form that the code is actually still processing. You are correct it is not a property that is exposed in design time only through code.

 

[mcelligott]

LOL. I used the MsgBox to indicate each step so I could trace/step through the program. That is how I determined that it was

DoCmd.Close acForm, "ECD Splash Screen"

Everything prior to that point very extremely fast. It was when it executed this statement it slow right down.

I should have shown where the different sections were, let me try it again.

Private Sub Form_Open(Cancel As Integer)

Dim strServerPath As String 'path to server
Dim strAltServerPath As String 'path to alternate ECD server
Dim strVPNPath As String 'path to ECD server via VPN connection
Dim strMCUPath As String 'path to MCU server
Dim strLocalPath As String  'path to local workstation
Dim strGFDPath As String 'path to Gates FD
Dim strPerintonPath As String 'path to Perinton Ambulance
Dim FSO 'File System Object
Dim response As String

Set FSO = CreateObject("scripting.filesystemobject")

strServerPath = "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb"
strAltServerPath = "\\fd01map\alternate server\ECD Operations Database_be.mdb"
strVPNPath = "\\10.100.91.3\database$\operations database\ECD Operations Database_be.mdb"
strMCUPath = "\\abernas\operations database\ECD Operations Database_be.mdb"
strGFDPath = "\\Dispatch_2\operations\ECD Operations Database_be.mdb"
strPerintonPath = "o:\ecd\ECD Operations Database_be.mdb"
strLocalPath = "c:\operations\ECD Operations Database_be.mdb"

If FSO.FileExists(strServerPath) = True Then
   Call pfECDServerReLink
ElseIf FSO.FileExists(strMCUPath) = True Then
   Call pfMCUReLink
ElseIf FSO.FileExists(strGFDPath) = True Then
   Call pfGFDReLink
ElseIf FSO.FileExists(strAltServerPath) = True Then
   Call pfAltServerReLink
ElseIf FSO.FileExists(strVPNPath) = True Then
   Call pfVPNRelink
ElseIf FSO.FileExists(strPerintonPath) = True Then
   Call pfPerintonReLink
Else
   Call pfLocalReLink
End If

lastline:
End Sub

Function pfECDServerReLink()

Dim dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim firsttbl As TableDef
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Set firsttbl = dbs.TableDefs("Alarm Companies")

'Determines if the current user is a special use or regular sign on
Select Case CurrentUser()
    Case Is = "MCU"
         Call pfMCUUser
    Case Is = "GatesFD"
        Call pfGatesFDUser
End Select

'Determines if the table Alarm Companies is already connected to the ECD Server
If firsttbl.Connect = ";Database=" & "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb" Then
    GoTo lastline
End If

'Loop through the tables collection and relinks to the ECD server if not already connected
MsgBox "Reconnecting to the ECD Server, this will take a few minutes.  Sorry for the inconvenience.", vbInformation
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
         Tdf.Connect = ";Database=" & "\\Ecd911\911\Database\Operations Database\ECD Operations Database_be.mdb"
         Tdf.RefreshLink
    End If
Next

lastline:

DoCmd.OpenForm "Switchboard", acNormal
DoCmd.Maximize
DoCmd.Close acForm, "ECD Splash Screen"

End Function
[\code]

 

[MajP]

I fully understand the code, no problem there. I just can not see anything that would cause a problem.

The only thing I can think is that there is some corruption in the form. Since this is a splash screen just hide it. Put a message box so you can see where you are actually connecting.

The code appears strange. Depending on which file exists you attempt to relink. However in one of the functions you call you close the splash screen and go to the switchboard, but all of the other functions do not do anything. So if you relink to ECDServer the splash will close. Any other server the nothing happens.

At a minimum this code should be in the open event. So in one area you relink then when complete you close the splash.
...
Else
Call pfLocalReLink
End If
DoEvents 'might not do anything.
MsgBox "Relink Complete" & strSource
me.visible = false
doevents 'just to be safe
DoCmd.OpenForm "Switchboard", acNormal
DoCmd.Maximize

Now close the splash before trying to open the switchboard. Just to make sure you know where you are connected. I would do something like

dim strSource as string
....

If FSO.FileExists(strServerPath) = True Then
Call pfECDServerReLink
strSource = "ECDServer"
ElseIf FSO.FileExists(strMCUPath) = True Then
Call pfMCUReLink
strSource = "MCU"
....

 

[mcelligott]

The other functions actually do the same thing as the pfECDRelink. I just did not include the others.

Question: When the program gets to DoCmd.Close acForm, "ECD Splash Screen" part, is it possible it is taking so long because it is trying to unload all the variables before closing the form?

 

[MajP]

Again try to hide the form as I described, in the order I described. If that does not hide the form, there is definitely something else going on.

There should be no issue with unloading the variables. That is instantaneous anyways, further you have very few and simple variable.

 

[MajP]

Does each user have a front end on their local computer?

 

[mcelligott]

Have added the code your referenced. Received an error for the following line "Me.Visible = False". The error says "Run-time error 2467: The expression you entered is closed or does not exist.

Prior to the error, I received the msgbox that stated relink completeECD Server.

 

[mcelligott]

Each users has their own front-end on their computer. The back-end is located in several different places. Everyone's front-end always looks for the back-end in the same order.

One of the things each person's front-end does is check for any programming updates to the front-end prior to opening. If it sees an update, it downloads that first and then opens the database continuing with the back-end link check.