CODEWITHSUNDEEP
ms-accessarchitectureclient-serverjet
Sagar Varpe
Sagar Varpe

Reputation: 3599

Will an MS Access back-end for a client server application have transfer speed problems?

If I use MS Access in the back-end of a client-server type software and the database file is sent from client to server, will it create any problems in further database handling, transfer speed, or performance compared to SQL Server?

Upvotes: 2

Views: 1565

Answers (4)

Albert D. Kallal
Albert D. Kallal

Reputation: 49264

I have a article on using access over a network, and especially that of a WAN here:

http://www.kallal.ca/Wan/Wans.html

Upvotes: 2

Doc Brown
Doc Brown

Reputation: 20044

Some good answers here, already. But something that is often overlooked is that there are scenarios where using a lightweight mdb gives you much more performance than a heavy-weighted SQL server. For example, if multi-user access is not so important, but you have to do a lot of batch processing on your data, using mdb files can be much faster. On the other hand, if you have a lot of classical OLTP processing with many users, you can benefit from a real client/server database.

Upvotes: 0

JohnFx
JohnFx

Reputation: 34907

If you use MS Access as your back-end database it isn't a client-server solution. Jet Databases (The kind MS Access creates) are file based, not client-server.

If the bandwidth between the client and the DB is high (like another server on the same network) then it shouldn't pose any major performance problems related to transfer speed. However, if you were connecting over a slow WAN link to the DB from the client, it definitely could introduce a performance bottleneck.

Upvotes: 5

Paul Keister
Paul Keister

Reputation: 13097

In my experience there are 4 major differences between MS Access MDB files and SQL Server performance in a small LAN based environment (where small means 20 users or less with no more than 10 concurrent user sessions)

  1. Security. Use of an access MDB file requires that the client have direct access to the MDB file. This architecture can't be truly secure if you need to limit data access for some users. Access user level security can be cracked. You can use file level or file share level security in the OS if that satisfies your security requirements.
  2. MDB files are subject to corruption as a result of network errors. The only time I've seen a SQL Server database become corrupted was as a result of hardware failure on the server.
  3. The upper limit for an MDB file is around 25 users, and Access is sensitive to high transaction volume for inserts, updates, and deletes.
  4. In most cases with Access you'll need to have all users sign out of the database to make any changes to the structure of the tables. This is much less convenient than using DDL scripts in SQL Server. If you decide to go with Access, I'd recommend getting a copy of LDBView so you can tell who you'll have to kick out of the database each time you make a routine change to the data structure.

There is a case to be made for a back end MDB file if the user audience is small and the simplicity of deployment is appealling to the client organization. But if you are starting a new project, the advantages of a SQL Server backend should be carefully considered. If you have a large user audience then SQL Server is strongly recommended.

It is unlikely that you will have a problem with transfer speed when using an MDB file with an up-to-date version of MS Access and well configured LAN.

Upvotes: 7

Related Questions