Help ! How well can MS Access handle Multiuser ?

[hiptino]

I'm currently developing a retail application.
The front-end is Visual Basic 6. The backend is Access 97.
The .mdb file contains just indexed-tables, relationships and security objects.
The front-ends access the back-end via mapped drives.

About 50 or more remote users will be updating/selecting/inserting rows concurrently in the .mdb file at a frequency of 30secs, from VB front-ends.

I used the following locking/cursor system:
-Select Statement(adOpenForwardOnly, adLockReadOnly)
-Insert Statement(adOpenKeyset, adLockOptimistic)
-Update Statement(adOpenKeyset, adLockOptimistic)

But I'm afraid MS Access might not be able to cope with such a high frequency of database activity, likewise the network traffic would be high.

a) Could MS Access cope with such high frequency database
activity ?
b) How many users can MS Access handle concurrently ?
c) How efficient is MS Access in multiusers scenario ?

I would appreciate any other advice as regards this.

 

[WP]

IMHO Access is a poor Multi-user back-end db.

I don't have exact figures for you....but gut reaction together with a wealth of experience tells me that your model above will not run smoothly.

The other downside to ACCESS 97 is the lack of server-side activity i.e. you cannot have stored procs. to cut down the network traffic.

If you can afford SQL Server then you would be well advised to go down that route. SQL Server is made for what you describe above.

Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[elizabeth]

"My general rule of thumb for an Access application that's not client-server is that with more than 10-15 concurrent users and more than 100,000 records, poor performance generally results." Alison Balter, Matering Access Development, page 5. HTH