Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 969
  • Last Modified:

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

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
pcalabria
Asked:
pcalabria
  • 7
  • 7
  • 6
  • +4
1 Solution
 
Barry CunneyCommented:
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
 
Simon BallCommented:
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
 
Simon BallCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Andrew_WebsterCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

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

JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The code is also attached in the Allen Browne link.

mx
0
 
pcalabriaAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
pcalabriaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
pcalabriaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
pcalabriaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
 
 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
 
pcalabriaAuthor Commented:
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
 
pcalabriaAuthor Commented:
Most important, thanks Joe!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome.

mx
0
 
pcalabriaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
NOT enough coffee yet!

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
  • 6
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now