best practices for multi-user environment & large data amounts

A year ago I started working in a small company that develops and sells an MS-Access 2000 application. This product deals with accounting and bookkeeping, inventory management, CRM, sales analysis, balancing, expenses, and so on and on and on. It took 7 years to develop. The developers are gone and I'm in charge for improvements, bug fixing and developing new ideas.
Most of our Clients are small retail shops or self-employed people. But there are some that are small or large companies as well. That's where the problem begins.

Every new year our clients archive their data and start a new database. Despite this fact, One of them has a huge database of 200 Mb, with 290 tables  some of them contain 150000 records within a single year.

The problem is: slowness!!

We've done everything we know that might improve their application-performance: our app is well split into backend db & a few frontend apps, the design is professional, the right fields are indexed, where we need too complicated queries for reports we use local/temp tables, each user has his own copy of the application & but still it is slow!

This is a large application: for example, the business-module consists of 666 forms, 300 reports, 509 queries and 11 modules.

Some people said to me: SQL server is the solution! Is it really?
I know nearly nothing about SQL server. In the past 9 years I worked entirely with Jet access applications.

Is it realistic to convert that complicated 7-year-development-application to SQL server.
And in the end  is it really going to work faster?

If that's so  what's best? To link to SQL server tables or to create an ADP?
What are the best books in the world that teach SQL server for access developers?

If SQL server is not such a great solution, what are the best practices or even the hidden secrets of making Jet Access apps work fast in a multiuser-environment & huge amounts of data?

(some more details: OS is win XP sp2. We use access 2000 because the app uses conditional-formatting in forms extensively, and Access 2003 has a bug with cond. formatting, as you surely know. Access 2007 hangs when we try opening/converting our staff to ACC2007, for a still unknown reason. So here we are, still with ACC2000. Our clients use it with the Access-Runtime. Number of users: 15.Network: simple file sharing network, and 2 users are sitted far away, connecting to their own copies of the app through a terminal-server).

Thank you in advance for your attention to my question,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

My recommendation is that you have probably pushed Access about as far as it can go, It would be well worth you looking to move up to SQL server (especially is the DB's are going to grow bigger over coming years). The problem is in the way access works (not being a full client server type application). If you have the budget it would probably pay you to buy in some consultancy to assist in the conversion. But when it comes to medium/large scale multi-user Access is really not the right engine.

Just my point of view.
I'd say the 'secret' to making Access run fast in a multi user environment is to be as frugal as possible with the amount of data that has to be transferred at any point.  Using filtered or unbound forms, minimising the use of combo boxes/list boxes that require indiscriminate population on form load.  But there are many other factors which can affect performance such as the network infrastructure, right down to the network cards the PCs use.

I'd suggest that you at least try SQL server.  You can get a free version e.g. SQL Server 2005 Express and providing the data volume doesn't exceed 4GB you should be ok.  I guess an ADP would be the 'ideal' way to go, but that might be onerous to redevelop.  A simpler first cut would be link directly to the SQL server tables, you can do this using DSN-less connections to minimise the administrative overhead.

I too have a client with an Access 2000 set-up.  For various reasons we're investigating a migrate to MSSQL. After having installed MSSQL 2005 express on their server, I migrated the back end data using the Microsoft SQL Server Migration Assistant tool (also free).   A few tweaks to the table connection procedure to enable DSN-less connections and it was up and running, and believe me, the general performance improvement was meteoric.  One or two of the queries required a re-write.

I'm sure the experts here will give you loads of tips on how to make improvements and the list of things you can do to improve Access performance is pretty long.  But for my money, in your situation, if you can spend a day, or maybe less and just 'test the concept'  you'll get an idea of the sort of perfomance gain you can expect for zero outlay.
SQL Server might help if the bottleneck is getting data.
But with size of your front-end app I'm not initially inclined to see the backend as the problem.
It would probably be worth testing the app with almost empty data tables to see if it runs noticeably faster.

Bear in mind that the performance benefit of SQL server applications arises as much from client-server design as it does from the power of the database engine. Forms are often unbound, or use recordsources which return individual records,  not tables.  Stored procedures are used instead of local procesiing in queries and vba procedure.

Given the size of your app,  though, the only short term possibility would be to change the backend and you could see how far you can get with the upsizing wizard as a first step.  But the prospect of testing such an application against a new backend would be mind-boggling.

I have to say that rejecting an upgrade to a later version of Access because of CF problems but being prepared to consider moving to an ADP sounds a bit out-of-balance to me. (Not that I think that a move to a later version would give you any performance benefit).
But with MS's current attitude towards ADPs i would have said it is not a good idea, anyway.

If the backend appears to be the issue, I think my own approach would be to see if there was a small set of key tables that could be migrated to sql server express using linked tables and see if the app (a) still works at all and (b) if there is any signifcant performance gain.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

    Your question is pretty wide open, but I'll mention a couple of quick things you can do:

1. Always make sure your apps are compiled.

2. Check you indexing again.  Keep in mind that indexing is a double edged sword; too little can be just as bad as too much.

  The more indexes you add, the faster searching goes, but then the longer adds, updates, and delete operations take and can lead to concurrency issues.

3. Of course it goes without saying that you need to make sure that the BE database is compacted on a regular basis.

4. Make sure none of the DB's (FE or BE) are being virused scanned.

5. The BE should be on a server that has OPLOCKS turned off.

6.  Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.

  Long term, there are some things you could do in VBA to speed up code.  Also look at some of your data search operations.  Any place where you can open a table as a table and seek on an index (which is extremely fast)?

  As far as moving to SQL server (or another BE RDBMS other then JET), yes it's something you should move to.  SQL Server and it's likes are far more robust then JET.  They also offer server side processing.

  Without too much trouble, you could move data to SQL Server, replace your current table links with ODBC connections, and be off and running.  Most will see a performance improvement, but there are those that will not without doing some work.

  Where the real performance gains come in is with the server side processing.  The first step is to change queries where possible to pass-through queries.  This is what it sounds like; you write SQL that gets passed through to the server and the data get's passed back or the action carried out server side.  The SQL is the servers SQL dialect, not Access's, so there are some differences.  

  Beyond that is stored procedures.  This is where you write a procedure server side and then simpley call it from Access.  


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  I should have added that out of the list of things that you can do quickly, #6 is the most critical and will yield the biggest performance gain depending on the app itself and how it's used.

NNOAM1Author Commented:
Well people, thank you so much for your comments. I'm beginning to feel I know what to do next. I guess the best thing is to try it with SQL server and see what happens. Thank you very much.
NNOAM1Author Commented:
What are a server's OPLOCKS?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OPLOCKS or opportunistic locking, is a client side caching system built into Windows.  Since JET has it's own caching already, OPLOCKs on a server simply get in the way and you may wish to turn them off.  You can read about them here:

How to keep a Jet 4.0 database in top working condition in Access 2000

  along with other things that might be useful to you.

NNOAM1Author Commented:
Well, my boss isn't going to consider using SQL-server for the time being. So we I have to provide some kind of improvement for our Acc2000 app. what does it mean "Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.
" - ?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Is this what you were looking for?Yes No
Well, my boss isn't going to consider using SQL-server for the time being. So we I have to provide some kind of improvement for our Acc2000 app. what does it mean "Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.
" - ?>>

  There's quite a bit of overhead that Acces/JET goes through to login a user to a database.  If your FE was developed in such a way or used that it closes all it references to a BE, then the user is logged out of the BE.

  To avoid this, in your startup code for the app, open a table on a global variable.  Then no matter what happens within the app, the user is always connected to the BE and the logout/login does not occur.   Close the connection before you quit the app.

  Depending on the app, this can speed things up quite a bit.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.