Why does it take my MS Access linked table so long to open?

Hi,
Once in the past I tried a similar setup.
I had tables in one .mdb file and front-end in another .mdb file.
I placed the front-end .mdb on a users PC and I placed the .mdb with the tables on the server in central network drive. I then linked in these tables to the front-end .mdb on the PC.
I was disappointed with the performance. In that particular situation I rverted back to having the tables and the application(forms, queries, code etc.) all in the same .mdb and simply place this .mdb in the central network location on the server for all users to access and this seemed to be the best solution peformance wise.

I designed other applications since this, but I used MS SQL Server Express as the back end and MS Access as the frontend. I designed all the business logic in Stored Procedures on the SQL Server.
I then called these Stored Procedures from VBA Code in the MS Access front-end application using ADO and dynamically populated controls(forms, combo boxes, listboxes etc.) from recordset objects returned by executing Stored Procedures via ADO.
To date this is the best solution architecurally and performance wise that I have come up with for an MS Access application.

So given that one of your tables contains 650, 000 records maybe a SQL Server back-end is something you should think about long-term.

In the meantime as a test it would be interesting just to put your tables and forms and all other objects in a single .mdb file and see what the difference in performance is.

If you have not tried already, try just opening the actual table in the front-end - as opposed to opening the table through a form.





 

Maybe the system is pulling all 650,000 records from the back end into memory of the xp pro pc?

For that many records i'd upsize to SQL server. and use ODBC to connect.

Do you need to open all the records?

sorry, i forgot press submit when i wrote that earlier.  BC's answer is much better than mine :)  i should have refreshed the page before submitting.

Access is a file server, SQL Server is a client server.  What that means is that Access opens entire mdb files, so it can mean large chunks of data being moved around your network.  SQL Server sits there waiting for a request to do something or serve up something, and when Access (or another client - VB, ASP, C#, whatever) talks to it, then it just sends what it's been asked for across the network.

So, on the whole, shifting to SQL Server is a good idea.

If that isn't an immediate option, then there are tricks you can use to make things appear to work more quickly:
1. Remove the form's recordsource so it opens immediately, then add it back in using the On_Timer event.
2. Load any combo boxes after the form opens using the timer again.
3. Minimize the amount of data that is being displayed by a form by basing it on a filter in another form.

They're just a few tricks that might work for you.

But long run... shift to SQL Server!

1. Check if the the BE is linked on a UNC path or a mapped drive.  UNC paths can cause issues.
2. Anyone else in the DB at the time?  If so, you may be running into OPLOCKs on the server.
3. Is your security correct?  You need full read, write, and delete privs for everyone for that directory that accesses that DB
4. Finially, make sure the BE is not being virus scanned by the server at open.

  Mx already hit the sub-datasheets, which should be turned off (code attached).

JimD.

Sub TurnOffSubDataSheets()

        Dim MyDB As DAO.Database
        Dim MyProperty As DAO.Property
        Dim propName As String, propVal As String, rplpropValue As String
        Dim PropType As Integer, i As Integer
        Dim intCount As Integer

10      On Error GoTo tagError

20      Set MyDB = curDB
30      propName = "SubDataSheetName"
40      PropType = 10
50      propVal = "[None]"
60      rplpropValue = "[Auto]"
70      intCount = 0

80      For i = 0 To MyDB.TableDefs.Count - 1
90        If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
100         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
110           MyDB.TableDefs(i).Properties(propName).Value = propVal
120           intCount = intCount + 1
130         End If
140       End If
tagFromErrorHandling:
150     Next i

160     MyDB.Close

170     If intCount > 0 Then
180       MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
190     End If

200     Exit Sub

tagError:
210     If Err.Number = 3270 Then
220       Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
230       MyProperty.Type = PropType
240       MyProperty.Value = propVal
250       MyDB.TableDefs(i).Properties.Append MyProperty
260       intCount = intCount + 1
270       Resume tagFromErrorHandling
280     Else
290       MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets RoutineName."
300     End If
End Sub

Select allOpen in new window

BTW, also make sure you've turned off name Auto Correct in Access options.

JimD.

The code is also attached in the Allen Browne link.

mx

Sud and BC,
unfortunately keeping all files in a single mdb is not an option, due to size limiations of access.  In addition, I need to frequently roll out new changes to the program code without effecting the data.

AndrewWebster, what I am saying is, if I open Access and am the only user in the application, if I go to the Access database window, select tables, and double click on this table, I see a white screen for 20-23 seconds.  After that time, the normal screen full of data that looks appears.

MX, I didn't understand your message but perhaps need to check the link first.  Where is SubdatasheetName, in the table, form, query, or somewhere else?  I found Subdatasheetheight.
Still checking.

Well if your front End is Los Angeles and your linked table is in Tristan da Cunha...
If your Network hardware is old...
... ;-)
Does another table from that same Back end also display a comparable delay?

Please look into the other Experts posts first.
As the goal here should be solving the issue.

As a test, you can always create a temp table in your DB of the linked table, on startup.
Then , *theoretically*, you will only have to tolerate the 30 second delay once as the temp table is created.

SELECT YourLinkedTable.* INTO tblTemp
FROM YourLinkedTable;

But again, please look into the other Experts posts first.
As the goal here should be solving the issue, not working around it.

;-)

JeffCoachman

What is the concensus about Name Autocorrect?

I checked all of my database files, front end and backend, and all have Track Name Autocorrect Info and Perform Name Autocorrect turned on.

I'm sure these were default settings.  Should I turn them off if every mdb file that I use?  I'm concerned that I'll introduce a new problem.

Thanks

<<I'm sure these were default settings.  Should I turn them off if every mdb file that I use?  I'm concerned that I'll introduce a new problem.>>

  Turn them off.  They've been buggy in the past and consume a lot of CPU cycles.  Not worth it.

<<MX, I didn't understand your message but perhaps need to check the link first.  Where is SubdatasheetName, in the table, form, query, or somewhere else?  I found Subdatasheetheight.>>

  Subdatasheet's are a table property.

@mx,

<<The code is also attached in the Allen Browne link.>>

  Not sure how I missed that!  Too much of a hurry this morning I guess.

JimD.

I carefully read the Allen Browne link and am confused.  Can I simply uncheck the "Track Name Autocorrect Info" and "Perform Name Autocorrect" boxes, or do I need to create new database objects and then import all objects into the new database.

For some reason, when I start Access and create a new database option, the File - Import option is grayed out.

<<Can I simply uncheck the "Track Name Autocorrect Info" and "Perform Name Autocorrect" boxes, or do I need to create new database objects and then import all objects into the new database.>>

  Yes.

<<For some reason, when I start Access and create a new database option, the File - Import option is grayed out. >>

  Not sure why that would be...

JimD.

"and all have Track Name Autocorrect Info and Perform Name Autocorrect turned on."
Turn The Off. Period."

" Where is SubdatasheetName, in the table"
It's on the Table Property Sheet in table design view.  
You will need to paste Allen's code into a vba module in that db, then call that function from the Immediate window.  Once done, you can delete code if you like.  However, you will need to run the code on any new tables you add ... as that property has to be set by code ... in order to 'stick'.

mx

Summary ... try the two items suggested - especially sub data sheets - I've seen the make HUGE differences ... and Name Auto Correct.  Both easy to do.
And we can go from there,

mx

Ok, I turned off all the Autocorrect options in all mdb files (FE and BE).

I also loaded the code provided by JimD (thank you, Jim), and get an error when I run it.  The error occurs when the code reaches the line:

Set MyDB = curDB

is:

The error message is :
Object Required
TurnOffSubDataSheets RoutineName

 
 Apologies.

  You can use the code at the link MX provided (which I totally missed) or simply change the line to:

 Set MyDB = CurrentDB()

or

  use the curDB() function:


Private objcurDB As DAO.Database

Public Function curDB(Optional bolRefresh As Boolean = False) As DAO.Database

10      If objcurDB Is Nothing Or bolRefresh = True Then
20        Set objcurDB = CurrentDb()
30      End If

40      Set curDB = objcurDB

End Function

 Which I covered in a article here:

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

JimD.

Thank you everyone for your help.

As MX initially suggested, the problem was with the SubdatasheetName.

Prior to running the code provided by MX from the Allen Browne link,  it was taking about 22 seconds to open the table if I were the only person in the database, and 35 seconds to open the table when there was one other user logged in.

After the code was run, the table openned consistently in 4 seconds, and this test was done with others logged in.

I'm not completely sure how to award points, but MX suggested this fix first.  I read his message and JimD's response at the same time, and since JimD provided code here on EE, I used his code first.  When the code did not work due to an issue with one of the lines, I tried the code in the artical provided by MX and all worked find.

JimD, thanks for getting back to me with the code correction, but by the time I received your message I had already used the code provided by MX.  Thanks for your help however.  It really appreaciate all the comments and suggestions!  Thank you.

Most important, thanks Joe!

<<JimD, thanks for getting back to me with the code correction, but by the time I received your message I had already used the code provided by MX.  Thanks for your help however.  It really appreaciate all the comments and suggestions!  Thank you. >>

  No problems here, Joe was definitely spot on with his first comment.

JimD.

You are welcome.

mx

Using the code provided int the SubDatasheetName section of the allenbrowne.com/bug-09.html reference reduced load time of my table from approximately 40 to 4 seconds.

When I first discovered this several years ago, the (auto) linking of approx 35 tables over a small LAN when form .... 5 minutes to .... 30 seconds !  Only as a result of just that change.

Forgot to mention.  IF you have a FE and BE setup, run that code in the FE on the linked tables - and local of any) also.  I don't know why but ... it will make *any* table ... say a simple local table with just a few records open *without* delay. So ... you may notice a local table take 2,3,5 seconds to open ... and you wonder why?  Set that property on that table .... and instant open.

mx

NOT enough coffee yet!

>>"LAN when form .... 5 "
LAN went from 5 ....