Local tables opening slow Access 2000

**TheSmileyCoder** · Aug 30 '12, 09:07 AM

Access can be a bit special sometimes, and this can be one of its quirks. Whether or not what I will suggest applies for you, I dont know, but here goes.

Imagine the scenario of a button which closes form a and then opens form B. If form A was the only form open, access will then release your connection to the backend, and when you then open form B access will have to re-establish the link. The same can actually apply even for a local database.

First a simple test to see if this applies to you.

Open your database while holding down Shift to override any startup procedures you have.
Open the table you say is slow in opening and note how long time it takes to open.
Close it again
Now open any other LOCAL table and leave it open.
Once again open the table that you say is slow.

If the second time was significantly faster then the first, the issue I describe is most likely what is affecting you. This effect will be larger if the frontend is not stored on your local PC but on a network drive.

I fix this by creating a table in my frontend, tbl_LocalStayCo nnected and in my backend tbl_ServerStayC onnected and add a single field to each (Field name irrelevant as long as the type is the same in each table.) In your frontend create a link to the tbl_ServerStayC onnected.

Now create a form frm_StayConnect ed and as its recordsource have:

Code:

SELECT * FROM tbl_LocalStayConnected 
         INNER JOIN tbl_ServerStayConnected on 
         tbl_LocalStayConnected.FieldName=tbl_ServerStayConnected .FieldName

Finally when the database starts op open the form in hidden mode so the user never sees it.

Code:

DoCmd.OpenForm "frm_StayConnected ", , , , , acHidden

This will ensure that a connection is always live.

One thing of note, is that it might degrade performance if you have several concurrent users, but for me, I haven't noticed any problems with 50 users at a time (havent been able to test with more then that)

**jonnycakes** · Aug 31 '12, 03:34 AM

Originally posted by TheSmileyCoder

Access can be a bit special sometimes, and this can be one of its quirks. Whether or not what I will suggest applies for you, I dont know, but here goes.

Imagine the scenario of a button which closes form a and then opens form B. If form A was the only form open, access will then release your connection to the backend, and when you then open form B access will have to re-establish the link. The same can actually apply even for a local database.

First a simple test to see if this applies to you.

Open your database while holding down Shift to override any startup procedures you have.
Open the table you say is slow in opening and note how long time it takes to open.
Close it again
Now open any other LOCAL table and leave it open.
Once again open the table that you say is slow.

If the second time was significantly faster then the first, the issue I describe is most likely what is affecting you. This effect will be larger if the frontend is not stored on your local PC but on a network drive.

I fix this by creating a table in my frontend, tbl_LocalStayCo nnected and in my backend tbl_ServerStayC onnected and add a single field to each (Field name irrelevant as long as the type is the same in each table.) In your frontend create a link to the tbl_ServerStayC onnected.

Now create a form frm_StayConnect ed and as its recordsource have:

Code:

SELECT * FROM tbl_LocalStayConnected 
         INNER JOIN tbl_ServerStayConnected on 
         tbl_LocalStayConnected.FieldName=tbl_ServerStayConnected .FieldName

Finally when the database starts op open the form in hidden mode so the user never sees it.

Code:

DoCmd.OpenForm "frm_StayConnected ", , , , , acHidden

This will ensure that a connection is always live.

One thing of note, is that it might degrade performance if you have several concurrent users, but for me, I haven't noticed any problems with 50 users at a time (havent been able to test with more then that)

Smiley, Thank you for your detailed response, but the second test(opening the local table while another local table was opened) did not do the trick. I'd like to also update my previous estimate of a 2 minute lag, it actually takes 4 minutes to to open this table.

I haven't tried the "stayconnec ted" method that you shared, but I am very interested. Currently, I'm using the code found here: http://www.fmsinc.com/MicrosoftAcces...dDatabase.html

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)
  ' 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 = "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

Do you or does anyone else see any downfalls with this code? How does it compare to your method smiley? Any other suggestions as for decreasing response time of this table? All other linked and local tables have been opening quickly.

This is odd, I exported the table's structure from the backend to the frontend, then from the front end, I copied the table(the one that was just added) and pasted it's structure, and it loaded almost instantly.

Any ideas? Thanks again!

**TheSmileyCoder** · Aug 31 '12, 07:38 AM

You are quite correct, the purpose of the FMS code is identical to mine, allthough the method is different.

I choose to do it through a form and its recordset because I felt it was a safer approach. If you have a unhandled error (Which of course you shouldn't, but still :P) the variables in your procedure will be wiped from memory, and my (unconfirmed) fear would be that you would by accident maintain a locking connection on your backend databases.

The form frm_StayConnect ed will remain open, even if a unhandled error occurs.

You could argue that their method is neater since it doesn't require extra tables to be setup.

However, I also code in the same form to do other various bits such as logging the user out when the form closes (Since its hidden the form only closes when the user closes the application)

Local tables opening slow Access 2000

Local tables opening slow Access 2000

Comment

Comment

Comment