Reputation: 964

C# and Access 2000

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?

Upvotes: 1

Answers (8)

Linger

Reputation: 15048

We have an Access database application that is used pretty heavily. I have had 23 users on all at the same time before without any issues. As long as they don't access the same record then I don't have any problems.

I do have a couple of forms that are used and updated by several different departments. For instance I have a Quoting form that contains 13 different tabs and 10-20 fields on each tab. Users are typically in a single record for minutes editing and looking for information. To avoid any write conflicts I call the below function any time a field is changed. As long as it is not a new record being entered, then it updates.

Function funSaveTheRecord()
   If ([chkNewRecord].value = False And Me.Dirty) Then
      'To save the record, turn off the form's Dirty property
      Me.Dirty = False
   End If
End Function

They way I have everything setup is as follows: PDC.mdb <-- Front End, Stored on the users machine. Every user has their own copy. Links to tables found in PDC_be.mdb. Contains all forms, reports, queries, macros, and modules. I created a form that I can use to toggle on/off the shift key bipass. Only I have access to it.

PDC_be.mdb <-- Back End, stored on the server. Contains all data. Only form and VBA it contains is to toggle on/off the shift key bipass. Only I have access to it.

Secured.mdw <-- Security file, stored on the server.

Then I put a shortcut on the users desktop that ties the security file to the front end and also provides their login credentials.

This database has been running without error or corruption for over 6 years.

Upvotes: 2

Tony Toews

Reputation: 7882

"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.

Upvotes: 2

Seth Spearman

Reputation: 6780

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. Creating the Front End MDE

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

Upvotes: 4

David-W-Fenton

Reputation: 23067

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.

Upvotes: 5

Chris Brandsma

Reputation: 11736

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.

Upvotes: 1

Aaron Fischer

Reputation: 21211

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.

Upvotes: 2

Fionnuala

Reputation: 91316

You may wish to read this informative thread about Access: Is MS Access (JET) suitable for multiuser access?

Upvotes: 4

John Saunders

Reputation: 161773

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

Upvotes: 1

C# and Access 2000

Answers (8)

Related Questions