C# and Access 2000

Question

I have developed an network application that is in use in my company for last few years. At start it was managing information about users, rights etc. Over the time it grew with other functionality. It grew to the point that I have tables with, let's say 10-20 columns and even 20,000 - 40,000 records.

I keep hearing that Access in not good for multi-user environments. Second thing is the fact that when I try to read some records from the table over the network, the whole table has to be pulled to the client. It happens because there is no database engine on the server side and data filtering is done on the client side.

I would migrate this project to the SQL Server but unfortunately it cannot be done in this case.

I was wondering if there is more reliable solution for me than using Access Database and still stay with a single-file database system.

We have quite huge system using dBase IV. As far as I know it is fully multiuser database system.

Maybe it will be good to use it instead of Access? What makes me not sure is the fact that dBase IV is much older than Access 2000. I am not sure if it would be a good solution.

Maybe there are some other options?

Answer 1

Access is not a flat file database system! It's a relational database system.

Answer 2

You can't use SQL Server Express? Otherwise, MySQL is a good database.

But if you can't install ANYTHING (you should get into those politics sooner rather than later -- or it WILL be later), just use you existing database system.

Basically with Access, it cannot handle more than 5 people connected at the same time, or it will corrupt on you.

Answer 3

You may wish to read this informative thread about Access: http://stackoverflow.com/questions/763888/is-ms-access-jet-suitable-for-multiuser-access

Answer 4

The problems you experience with an Access Database shared amongst your users will be the same with any file based database.
A read will pull a lot of data into memory and writes are guarded with some type of file lock. Under your environment it sounds like you are going to have to make the best of what you have.

Answer 5

If you're having problems with your Jet/ACE back end with the number of records you mentioned, it sounds like you have schema design problems or an inefficiently-structured application.

As I said in my comment to your original question, Jet does not retrieve full tables. This is a myth propagated by people who don't have a clue what they are talking about. If you have appropriate indexes, only the index pages will be requested from the file server (and then, only those pages needed to satisfy your criteria), and then the only data pages retrieved will be those that have the records that match the criteria in your request.

So, you should look at your indexing if you're seeing full table scans.

You don't mention your user population. If it's over 25 or so, you probably would benefit from upsizing your back end, especially if you're already comfortable with SQL Server.

But the problem you described for such tiny tables indicates a design error somewhere, either in your schema or in your application.

FWIW, I've had Access apps with Jet back ends with 100s of thousands of records in multiple tables, used by a dozen simultaneous users adding and updating records, and response time retrieving individual records and small data sets was nearly instantaneous (except for a few complex operations like checking newly entered records for duplication against existing data -- that's slower because it uses lots of LIKE comparisons and evaluation of expressions for comparison). What you're experiencing, while not an Access front end, is not commensurate with my long experience with Jet databases of all sizes.

Answer 6

For the record this answer is copied/edited from another question I answered.

---

Aristo,

You CAN use Access as your centralized data store.

It is simply NOT TRUE that access will choke in multi-user scenarios--at least up to 15-20 users.

It IS true that you need a good backup strategy with the Access data file. But last I checked you need a good backup strategy with SQL Server, too. (With the very important caveat that SQL Server can do "hot" backups but not Access.)

So...you CAN use access as your data store. Then if you can get beyond the company politics controlling your network, perhaps then you could begin moving toward upfitting your current application to use SQL Server.

I recently answered another question on how to split your database into two files. Here is the link. http://stackoverflow.com/questions/1147702/rookie-ms-access-creating-the-front-end-mde/1147858#1147858

Splitting your database file into front end : back end is sort of a key to making it more performant. (Assume, as David Fenton mentioned, that you have a reasonably good design.)

If I may mention one last thing...it is ridiculous that your company won't give you other deployment options. Surely there is someone there with some power who you can get to "imagine life without your application." I am just wondering if you have more power than you might realize.

Seth

Answer 7

"Second thing is the fact that when I try to read some records from the table over the network, the whole table has to be pulled to the client. "

Actually no. This is a common misstatement spread by folks who do not understand the nature of how Jet, the database engine inside Access, works. Pulling down all the records, or excessive number of records, happens because you don't have all the fields used in the selection criteria or sorting in the index. We've also found that indexing yes/no aka boolean fields can also make a huge difference in some queries.

What really happens is that Jet brings down the index pages and data pages which are required. While this is a lot more data than a database engine would create this is not the entire table.

I also have clients with 600K and 800K records in various tables and performance is just fine.