[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Speed up Access linked tables

Posted on 2011-05-06
5
Medium Priority
?
328 Views
Last Modified: 2013-11-05
Hi Experts,

I have to mdb files. One that is used as front end (is placed local on the users computer) and one mdb file with data that the users work with. All tables are therefore linked. The mdb file with the data is placed on a network drive, and is therefore a bit slow. But i found out something funny. If i add 14000 rows from a linked table (the data mdb) to a local table (the front end mdb) it takes about 16 sec. But if i do the same procedure while having the data mdb open it takes 1 sec.

That is fast, but is there a way I can have this data mdb open so the system is fast without the users can see it?
0
Comment
Question by:DCRAPACCESS
  • 3
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 35705505
<<That is fast, but is there a way I can have this data mdb open so the system is fast without the users can see it? >>

  You should always keep a connection open to the BE MDB.  Simplest way to do that is open a bound form at startup of the FE and don't close the form till the app quits.

 Or you can open a recordset in code:

        Dim rstBE1 As DAO.Recordset
       
        ' Open backend table in JET based MDB/MDE for performance.
        Set rstBE1 = curDB().OpenRecordset("tblSyslog")

  This avoids the repeated opening and closing of the BE DB.  

JimD.
0
 

Author Comment

by:DCRAPACCESS
ID: 35705607
I will try
Simplest way to do that is open a bound form at startup of the FE and don't close the form till the app quits.

But what about ODCB connections?
0
 

Author Comment

by:DCRAPACCESS
ID: 35705631
That does not seems to work, i have a form open/(in form view) that is bound to a dummy table that is in the BE. But it is still uses 16 sec to insert 14000 records into the linked table :-(
0
 
LVL 58
ID: 35705720
<<That does not seems to work, i have a form open/(in form view) that is bound to a dummy table that is in the BE. But it is still uses 16 sec to insert 14000 records into the linked table :-( >>

<<If i add 14000 rows from a linked table (the data mdb) to a local table (the front end mdb) it takes about 16 sec. But if i do the same procedure while having the data mdb open it takes 1 sec. >>

  I'm not sure I follow then what your doing.

  Don't forget, JET has an local page cache it maintains.  If your repeating a test, there is a good possibility that all the records are in the cache and it will be extremely quick in that case.

  Also, JET may cache data in local temp files depending on the operation.  For example, if you create a snapshot type recordset, all the records you request are copied to a local file.

<<But what about ODCB connections? >>

  Depends on what type of ODBC connection we are talking about.  SQL Server, mySQL, Oracle, etc all operate differently then a JET based DB (a MDB file).

JimD.

0
 

Author Comment

by:DCRAPACCESS
ID: 35705786
You are right about:
Don't forget, JET has an local page cache it maintains.  If your repeating a test, there is a good possibility that all the records are in the cache and it will be extremely quick in that case.

  Also, JET may cache data in local temp files depending on the operation.  For example, if you create a snapshot type recordset, all the records you request are copied to a local file.

I restarted the FE mdb and then it worked :-) 1 sec inserting 14000 records :-)
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

873 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