Multi-User? JET? SQL Server?

[Knowledge]

I have been charged to solve a problem in an Access database someone else programmed, but before I do anything to the database, I need a little bit more information(I'm an Access newbie). Here's my situation :

My client has an Access database on the Server. There are three persons currently using this database. The problem is that to open the database they are all double-cliking on the same icon on the server. (which I think is WRONG...) Recently the database has been corrupted when two persons accessed to it at the same time.

Is Access multi-user? How can I set them up so they can all access to it at the same time? I was looking at the documentation about JET and SQL Server but I'm confused....

What I understand of it is I have to optimize the Access database to an Access Project so it can be shared to all three users. Do they need to buy SQL Server for that?

Please someone put me on the right track....I would really appreciate any kind of help!

Thanks!!

 

[Pat Hartman]

You don't need to convert the .mdb to a project to optimize it. You didn't mention which version of Access your client is using. If it is A97, that version allows multiple users to make design (not just data) changes while other users have the db open. This has a tendency to cause corruption and so is NOT recommended. Newer versions of Access limit db users to 1 if any make design changes.

First thing you need to do is to split the db into a front end with all the forms/reports/queries/macros/modules and a back end with ONLY tables. The fe links to the tables in the be. The be sits on the server and is shared by all users. The fe can also be located on the server or it can be placed on the users desktops. The desktop deployment makes for the most stable setup and also minimizes the potential for corruption.

 

[Knowledge]

Thank you Pat! It helps me a lot!!

How would I split the database? Is there some kind of wizard that does that for me or I would have to do this manually? If that's the case, how would I do that?

And after the databases are split in two, I put the database containing the data on the server and every one gets a copy of the front end on their desktop, right?

Thank you SO much for your help!!

 

[Knowledge]

Okay, I found the way to do it. It works perfectly!

But what happens if 2 persons are trying to modify the same record at the same time?

 

[Pat Hartman]

Whenever an Access db is opened, Jet creates an .ldb file with the same name as the .mdb in the same directory as the .mdb. Jet uses this db to keep track of who is doing what in the db. So if two users try to change the same record, one of them will get an error and will be given three choices - discard changes, save to clipboard, overwrite the other person's changes.

This is actually a very rare occurance. Although many people may be updating records in the db at the same time, it is really unlikely that they would attempt to update the same record at the same time.

 

[Knowledge]

Thanks! Your help is really appreciated.