Setting Up a Database for Multiple Users

[captainnapalm]

I am trying to set up a database which will have multiple administrators. According to Microsoft, if I write a database and put the file in an area on the network, whoever opens it first will have "ownership" of the file (much like a Word or Excel document) and the second person will not be able to edit the file or add records. Microsoft recommended installing Microsoft Access 2002 onto the network server. They say that this will act as a database server and will allow multiple users to update records at the same time, as long as all the users are using the same version of Access (i.e., 2000 won't work with 2002, etc.).

Now, my questions are:

1) Is it possible to set up a database file to allow simultaneous multiple user access without requiring a server?

2) If it is deemed necessary to install a copy of Access 2002 on the network server, how does that work and how does it need to be set up? Microsoft is telling me that it doesn't require SQL Server to do this, although SQL Server would make processing faster.

3) Does anyone have any recommendations as to how to accomplish the goal I have in this whole ordeal (i.e., one database set up somewhere that multiple people can access, update, and print reports from simultaneously)?

Any recommendations, comments, or help would be greatly appreciated!

Thanks,
Scott - aka CaptainNapalm

 

[Hap007]

Captainnaplm,

Sounds like you received some questionable answers from Microsoft. Hopefully they miss understood your questions based on the answers you received.

1) To share an Access database, you need to locate the database on a shared drive and directory. This might be on a server or one of you local PCs that has sharing enabled. Note, locating on a server is best.

2) No, you do not need MS access on the server if you choose to locate the database that way. You do need MS access on each user's PC that will run the database.

3) How:
a) Makesure your setting/default open method is not set to exclusive. That way multiple users can access the database.
b) You should have the code/program database local for each user and the data on the shared disk using file links.
c) Make sure that access opens files in Non exclusive mode.

Hope This Helps,
Hap
Access Add-on developer

http://www.fcs-software.com

-

 

[captainnapalm]

Hap007 - The files will be located on the server and we have set the file to be shared. We also set up Access 2002 to be non-exclusive. When we test the database, however, all we get are the typical MS Office "These files are in use by (such-n-such) (Read-Only) (Notify) etc.".

Also, would there be any benefit to running Access 2002 on the server? Is there anything that would help?

I have a feeling we're doing something wrong on this end. We do have a spare Access2002 installation we can put on the server. Just trying to get everything done correctly.

Thanks,
napalm

 

[Hap007]

No benefit to running Access on the server, except/unless if you want to do work or change at the server console.

Access is really 2 things in one.
1) Access can hold tables and your data.
2) Access can be programmed using forms, queries and VBA code.
In a multi user environment, you want the pgromas in a different mdb from the data. Each user has his own copy of the access mdb program. The access data mdb is the only shared piece.
The access Program mdb sees the live data tables by having a link setup from each table in the program linked to the desired table in the shared database.

Hope this helps,
Hap
Access Add-on developer

http://www.fcs-software.com

-

 

[captainnapalm]

Hap007,

Having a very limited total amount of knowledge on Access, how do I set up the code for the DB in a file separate than the actual database? You're saying a link, but how does the link work? I guess the right question would be - is there a website that describes how to do this? I'm computer oriented, but I haven't delved into the realm of Access until very recently.

Thanks for your help up to now, by the way.

captainnapalm

 

[lasche]

Hap007 is right on track talking about having seperate databases. One with the tables, one with the forms, etc to avoid that read-only error. I am not sure on the setup, but I know that's the first step to getting it to work. Good Luck!

 

[Hap007]

captainnaplm,

I wish I could offer more help, but I think what you ask is beyond the scope of one question on the formum.

You need to research and then ask specific questions regarding this.

Brief Overview:
You have your table, that hold information in one mdb (This is called the backend)
You have your forms, code, queries and other executable logic in another mdb(This is called the Frontend)

You then open the Frontend, and the source for the tables that hold your data are then linked to the Backend. You would use the Access Menu to do this the first time.

I beleive that there is a wizard in Access to do this for you.

Notes:
1) If all of this makes no sense, then you need a book or something to describe the concept.
2) Before you do this, make sure you backup your original data/program.

Final Thoughts:
1) If your program and data are all in one database, what would happen if you want to make a programming change. You would have to ask all the users to not use the program while you spent a day or week changing the program. Linked tables solve this.
2) Sometimes Access databases become corrupt. This is much more likely to happen in the code area then the data area of the database, so spliting the two provides a saftey net for your data.
3) You can then share data with other users. If the program is in the same database and two users run the same logic at the same time, results will be unpredictable.

Hope This Helps,
Hap Access Add-on developer

http://www.fcs-software.com

-

 

[RGordy]

To Link tables -
Set up one database with just tables, no forms etc.
The create a second database. Working in the second database, click on the file menu item (top left corner of screen). On the drop down menu you will see "Get external data". In a second or two the sub menu item "Link Tables" will appear. Click on this and then follow the steps. You will just have to give the path to the database with the tables. Then you will select the tables you want, or "all". After you say go, the tables will appear in the database you have open. You can then treat them like one database.

The good part about this is that you can update the "program" database without any fear of corrupting the database that contains all of your data.