Local tables opening slow Access 2000

[jonnycakes]

Hello again to my favorite forum,

So, I have an Access 2000 split database, the backend (mdb) is stored on a shared drive while my front end(mde) is stored on my local machine. I'm experiencing a long wait time when attempting to open local tables and linked tables.

I found this article in many places during my search for a solution: http://bytes.com/topic/access/answers/728919-slow-opening-local-tables-access-2003-a

I followed Allen Browne's and Microsoft's suggestions by changing the SubDataSheet Name property to None on each of my tables, and it helped to a degree. However, the table I have with 2500 records, approx. 20 columns(text, dates, memos, yes/no) can take up to 2 minutes to populate in my front end, but my backend loads the table instantly.

What's really odd about this is that I've exported this table's structure(no data) from the backend to the front end, and removed the link to rule out the network. In other words same table, unlinked(local), no data, and it still takes a minute or two to load. It says "beginning save procedure" in the lower left hand corner when the database is loading the table.

Any ideas you may have to remedy this annoyance?

Thank you in advance for any knowledge you may be able to share.

[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.

1. Open your database while holding down Shift to override any startup procedures you have.
2. Open the table you say is slow in opening and note how long time it takes to open.
3. Close it again
4. Now open any other LOCAL table and leave it open.
5. 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_LocalStayConnected and in my backend tbl_ServerStayConnected 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_ServerStayConnected.

Now create a form frm_StayConnected and as its recordsource have:

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.

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]

@TheSmileyCoder
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 "stayconnected" method that you shared, but I am very interested. Currently, I'm using the code found here: http://www.fmsinc.com/MicrosoftAcces...dDatabase.html

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]

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_StayConnected 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)