Solved

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

Posted on 2011-03-01
25
911 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
  • 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
 
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 - Access MVP) 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now