Hi Everyone,

I have a database currently which is split, however since the split performance has dropped dramatically!!! now the database is in a multi-user enviroment with up to about 20-30 concurrent users at any given time. The data database size is around the 10mb, with the front end around 3-6mb. I know the general rule is split a database in a multi-user enviroment, but do you guys think a database of this size is worth the performance lag.

General Info =
highest record count in single table = approx 9000
Table count = approx 15
queries are stored in the front end only where most of the forms get their data.
transaction based database, i.e users create transactions
vba code runs all bar one of the forms
life cycle of database 12-18 months
estimate database size at end of life = 600mb - 800mb

Any of your thoughts and experience would be great

Cheers
Darren

I was running my Access db in my company for last 6-7 years. One of its table holds more than 140,000 records. Db contains lots of forms and queries with many reports and modules was successfully running.

Two of our users are about 70 mtr away from main server and were still receiving data at normal speed.

Only in mid of 2007 we started using Delphi but until that movement there was no problem at all to my Access db. The db size grown up to 14 MB and I still found no problem with it. I believe if back end is SQL Server and developer uses ADO method, there will be no problem at all.

Ashfaque

The speed issue may not be a problem with the split itself, per se. It could be that your network is having issues with the data tranfer from from the network to the users' C: drive.

Also, take a look at your VBA programming and your queries. If you have intensive querying out to the network, or if your VBA functions consistently tap into the tables "out there," that could slowing you down.

Another thing in your programming and queries that can slowing you down is how efficient they are. For example, the functions DLOOKUP, DCOUNT, DMIN, DMAX, etc. may be easier to use than creating recordset objects in VBA and using them, but they are much less efficient. The same operation using DLOOKUP vs. using a recordset object is usually much slower in the former than the latter.

I had that issue, and what I did was avoid using DLOOKUP if I could help it. Also, I had a start-up routine in the front end's MDB make local copies of data tables so that when they queried things, it went MUCH faster.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division