Maintaining persistent connections to linked tables could improve performance significantly because it prevents Microsoft Jet from constantlydeleting, creating, and obtaining locking information from the other database's locking information file.

Public rsAlwaysOpen As Recordset

Private Sub Form_Close()

rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("DummyTable")
End Sub

If the above doesn't work then try turning off the AutoCorrect as mentioned in one of the following KB articles. One person stated this made a huge difference in permformance.

Then read carefully through the following list of KB articles. Thanks to MS for compiling this list.

Access 2000 performance may not be as good as it was in Access 97 for a number of reasons, most of which are outlined in the following articles:

Q261000 - ACC2000: Slower Performance on Linked Tables

http://support.microsoft.com/support/kb/articles/q261/0/00.asp

This article indicates that if the database has many linked tables that also have many relationships, and the table that you are opening has its sub datasheet Name property set to [Auto], this can make the table slow to open. Subdatasheets are a new feature in Access 2000. Therefore, you are more likely to notice this behavior after you convert a database from an earlier version.

It is recommended that we set the sub datasheet Name property on each table in the back-end database to [NONE] .

The following articles address performance improvements that can be obtained when using Access 2000. Each of these suggestions should be reviewed and applied as appropriate to obtain maximum performance from Access 2000.

Q209113 - ACC2000: Tips for Improving Sub form Performance

http://support.microsoft.com/support/kb/articles/q209/1/13.asp

Q209126 - ACC2000: How to Optimize Queries in Microsoft Access 2000

http://support.microsoft.com/support/kb/articles/q209/1/26.asp

Q210408 - ACC2000: How to Speed Up Iterative Processes in Visual Basic

http://support.microsoft.com/support/kb/articles/q210/4/08.asp

Q248910 - ACC2000: Opening Form 100s of Times Affects System Resources

http://support.microsoft.com/support/kb/articles/q248/9/10.asp

Q240434 - HOWTO: Improve Performance of Applications Using Jet 4.0

http://support.microsoft.com/support/kb/articles/q240/4/34.asp

Access 2000 takes significantly longer to open a complex query in design view or to make design changes.

Access 2000 does experience a performance decrease (and a related increase of the database size) as compared to Access 97. This is caused by the new way Access 2000 stores project items. Project items consist of Forms, Reports, Macros and Modules. In previous versions, each object had its own record in the system table. If a change was made to an object only that one record in the system table was updated.

With the move to include the Visual Basic Editor interface, we now store all project items as one blob within approximately one record in the system table. If there are lots of code, forms and reports, then making a change to 1 object causes us to rewrite the majority of the blob that consists of all the project items. As a result, more is being written to disk then was done in the past.

Some changes to the database cause us to make a copy of the project items instead of replacing the old project which can cause an increase in database size. If we have a large project and we end up copying it then we double the size of the project within the database. For example, lets say we have 10 MB project and perform an action that causes us to make a copy of the project instead of replacing it, the database will grow by 10 MBs. Compacting the database at this point should recover the project no longer being used and should reclaim some space (if not all 10 MBs).

The best choice to reduce the impact of this change is to do all the development of the database with all the database files located on the local development machine and not on the server.

For more information, please see the following article:

Q200600 - ACC2000: Slow Performance Opening Object with Name AutoCorrect

http://support.microsoft.com/support/kb/articles/q200/6/00.asp

Q246306 - ACC2000: Saving Objects in DB Slower Than in Earlier Versions

http://support.microsoft.com/support/kb/articles/q246/3/06.asp

Tony
--
Tony Toews, Microsoft Access MVP

Please respond only in the newsgroups so that others can read the entire thread of messages.

Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm

From: David W. Fenton (dXXXfenton@bway.net) Subject: Re: Speed Issues Over Network (yawn) View: Complete Thread (18 articles)
Original Format
Newsgroups: comp.databases.ms-access
Date: 2002-10-14 08:03:40 PST

fletch@home.com (Fletcher Arnold) wrote in <aoclvi$mlt$1@knossos.btinternet.com>:

>I know performance over a network has been discussed many times >and I have spent a long time going through previous posts before I >decided to ask. A particular form "frmCompanies" is taking a >minute, sometimes more, to load. Switching to design view can >cause it to stop responding indefinitely. I pulled apart the form >and put it back together bit by bit and managed to get it to load >in about 4 seconds. I then changed some unrelated line of code, >re-compiled and performance dived. Changing the code back did not >improve speed. When I open the form, I see the lights on the hub >flash madly for a few seconds, then 30-40 seconds of nothing. >This can cycle for a few times before the form loads. >My db is written with A2K and is split be & fe, although the users >have Access XP. When development is finished, I intend to create >an XP mde file - but for the moment the front end is an A2K mdb >file.
>
>Any similar experiences? Ideally with resolutions.

Yes, plenty of similar experience.

The first thing to do is to delete and recreate the linked tables. Access2K caches a lot of data about table links that previous versions did not, and it can lead to big performance hits. This is most likely to happen in situations that developers encounter, where you develop on your own system and then send to the client for deployment on their network, and you relink the tables by changing the connect string (e.g., using the Linked Table Manager, or using some kind of relinking code).

The second thing to do is to determine whether or not your form is simply loading a helluva lot of data. If there are subforms that are being loaded but cannot be seen when the form loads, then those are candidates for "just-in-time" loading. That is, don't load the subform until it is actually displayed. I use hidden tab controls to drive most of my complex forms, and load the subforms on most of the tab pages only when the page is displayed. This is accomplished with a CASE SELECT in the tab control's OnChange event.

You can do the same evaluation for combo and listboxes, but this is not necessarily as big an issue. I more and more leave combo boxes with no rowsource until a couple of characters have been typed (using the combo box's OnChange event, and testing for Len(.Text)). This means you are not hitting the database at all when you load the form, only when the user is actually using the combo box, and it also means that you're returning a much smaller result set (how much smaller depends on your data). Obviously, this is only relevant with combo boxes based on tables with large numbers of records (or SQL strings with lots of outer joins).

Also, evaluate the efficiancy of all recordsets retrieved when the form opens. The more joins you have (especially outer joins), the bigger hit you'll take. I've been using a rule of 3 on this, trying to make sure that no query has more than 3 non-hierarchical outer joins. That is, if you have one table with 3 other tables LEFT joined directly to it, then you're likely to see problems (though it might depend on your criteria -- if you have criteria on the outer join tables, you'll likely not see as big a performance hit; of course if your criteria there are not "something or Is Null" then you really should be using an inner join in the first place, as the result will be no different). Also, if you have criteria on calculated expressions, you'd be better off doing something to get rid of those. And, of course, evaluate whether or not you have appropriately indexed all your fields on which you have SELECT criteria.

It's amazing how much of a difference in performance you can get simply by making your recordsources more efficient.

And if you're not sure where the hit is, you can try enabling SHOWPLAN, which will show you how Jet is executing your SQL (look that up in the MS Knowledge Base for instructions). The only thing that is a disappointment about SHOWPLAN is that reporting on subquery optimization is not including in it. As I use lots of subqueries, this is a real deficiency for me.

--

David W. Fenton                        http://www.bway.net/~dfenton
dfenton at bway dot net                http://www.bway.net/~dfassoc