0

Can someone explain to me how are effected the performances of an Access 2003 DB when it's used by a lot (30) of users on the same LAN?

I'm working on a vb6 project with this access 2003 DB wich performs ok on my local PC, but it's terrible when used across 20-30 users.

It's there something I can do to improve performance? How can I understand what's the cause of this degradation in performance?

  • See granite.ab.ca/access/performancefaq.htm But in general : Access is NOT a real client/server database, for having >30 users at the same time accessing your DB, you should think about switching to a system like MS SQL server, Oracle, Postgres, MySql, Sybase etc. – Doc Brown Jun 8 '14 at 12:00
  • Is this an old project you're trying to move from a desktop to multiple users? Trying to figure-out why a new project would get started in VB6. – JeffO Jun 8 '14 at 16:02
  • No, it's an old project that can't be moved to sql server yet and so I'm trying to speed it up a little bit. – SilentRage47 Jun 8 '14 at 16:05
2

Main problem of a shared Access database is that each client is modifying the physical database file(s) directly. That means for example that when one of the clients wants to update record, it must make a lock on that record - writes that information into a file (slow operation, particularly when its done over network) for other clients to acknowledge it, writes the change and releases the lock (another disk operations over a network). Whreas when this operation is done on databases like SQL Server, only commands are sent by clients over the network, and the database system in question handles the operation - and it is WAY faster because on its end all operations are made directly, not over network and can be far better optimized (eg. locks/records stored in memory).

What you can do to improve performance: 1) move the database to SQL Server if it is possible - really, you save yourself alot of trouble 2) the faster the network it runs on is, the better - for 30 clients 1000Mbps is a must (it also depends on database size) 3) if any client accesses the database over WiFi its BAD - it slows the database down, and risk of database file corruption increases alot - again; the bigger database file, the worse 4) index for performance 5) do "Compact and repair database" often - at least once overy month on databases >100MB

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.