Solved

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

Posted on 2011-03-01
25
951 Views
Last Modified: 2012-05-11
Hello,

I have an access application with split front and backends.

One of the backend database files is called WI.mdb.
This database includes two tables, WIversion and WIstock.
WIversion only includes one record, however, WIstock inlcudes 650,000 records.

When I double click on the WIstock from the Access Database window it takes 20-23 seconds before the information appears on the screen.
The frontend is located on an XP Pro workstation and the WI.mdb file is located on a Windows 2000 server.

I have tried keeping open a second form (tied to the WIversion table), but regardless of whether the second form is open or not, the time that I have to wait to open the WIstock table is not changed.

This seems like a long time to wait.  It also takes a very long time to open a recordset created from this database.
Is there anything I can do to speed things up?

0
Comment
Question by:pcalabria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 6
  • +4
25 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35005509
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.





 
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35005618
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?

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35005626
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35007460
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!
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 35007468
Most likely the problem is the Sub Datasheets property in tables.

http://allenbrowne.com/bug-09.html

Scroll down to 'Tables: SubdatasheetName'

Be SURE this property is set to None for ALL tables, both in the FE and BE.

You many need to use the code shown to set the property.  This makes an AMAZING difference when linking for sure, and opening a table.
mx
0
 
LVL 57
ID: 35007693

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

Open in new window

0
 
LVL 57
ID: 35007717

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

JimD.
0
 
LVL 75
ID: 35007803
The code is also attached in the Allen Browne link.

mx
0
 

Author Comment

by:pcalabria
ID: 35007906
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35008211
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


0
 

Author Comment

by:pcalabria
ID: 35008311
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
0
 
LVL 57
ID: 35008443
<<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.
0
 

Author Comment

by:pcalabria
ID: 35010651
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.
0
 
LVL 57
ID: 35010905
<<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.
0
 
LVL 75
ID: 35012360
"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
0
 
LVL 75
ID: 35012519
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
0
 

Author Comment

by:pcalabria
ID: 35013577
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

0
 
LVL 57
ID: 35013778
 
 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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

JimD.
0
 

Author Comment

by:pcalabria
ID: 35014179
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.
0
 

Author Comment

by:pcalabria
ID: 35016158
Most important, thanks Joe!
0
 
LVL 57
ID: 35016571
<<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.
0
 
LVL 75
ID: 35017944
You are welcome.

mx
0
 

Author Closing Comment

by:pcalabria
ID: 35018276
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.
0
 
LVL 75
ID: 35018891
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
0
 
LVL 75
ID: 35018935
NOT enough coffee yet!

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question