CODEWITHSUNDEEP
ms-access
Baper
Baper

Reputation: 1543

Improving Performance in an MS Access Database

I have an MS Access 2003 Database with more than 60 tables and 120,000 records. This DB works on the network and it is split into a back end(BE) and a front end(FE). At most two or three users use this DB at the same time.

I want to improve the performance as it is currently quite slow. Which approach will improve performance - using FE/BE or putting the whole DB on a shared folder (without FE/BE) and then using it?

Upvotes: 2

Views: 1679

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

Under no circumstances should you run an unsplit MS Access app in a multiuser environment. If your app is slow, the article posted by vulkanino is a step on the way to making it faster. You should make sure that all you tables have suitable indexes, that queries use those indexes as far as possible, that forms are based on queries, not tables and that complex forms are carefully organised. For example, subform controls can be loaded with forms as required.

Upvotes: 4

Steve
Steve

Reputation: 216353

From my experience, none of your solutions will work well. You should use a sqlserver (full or express) as your backend. Then connect with odbc from the front-end. After that, look carefully at your indexes and try to move the most heavy query in storedprocedures or views in sqlserver. This will save your work on forms, reports and vba code.

Of course this scenario assumes a lot about your network (do you have a always on server pc?), about your work and purpose of your db.

Upvotes: 1

Related Questions