• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

Cannot use a microsoft access database with multiple users

I have a microsoft access database.  I have four computers running access 2003.  I am networked with a workgroup and have the database in a shared folder.  I would like all four computers to access the database at the same time.  

Currently only one user can use the database at a time.  When a second computer tries to open the database it refuses to open.  We do not get any error messages.  

Any ideas what may be causing this?
0
Trevor007
Asked:
Trevor007
  • 3
  • 2
  • 2
  • +4
4 Solutions
 
omgangIT ManagerCommented:
Database property is set to open in Exclusive mode?  Tools --> Options --> Advanced    Check 'Default Open Mode'

OM Gang
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must also set Folder permissions correctly. Users must have (at least) Modify permissions for the folder hosting your backend database ... and those permissions must also be inheritable, since access must create a lockfile.
0
 
BIGELOJCommented:
I am no expert but I have come across this before in my own development. I have copied the database to each of the perspective client computers and then "linked" the tables to the main database on the server. This allows multiple users to use the same data and increases performance as the queries/forms etc...  are run on each individual computer and only the data is transmitted over the network.

I hope this helps.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
zoom2000Commented:
I suggest that you change the model you are using

I mean you split your application into 2 databases  

Frontend database contains the coding , forms and reports ....
Backend database contains the tables and queries.

The tables in the backend is linked tables in the frontend

Put the backend on the server on a shred folder , as you are doing now.

Put the frontend database in each client pc

this is a very good solution for many annoyances that often happens during opening the same database by many users on network


0
 
GRayLCommented:
You can never 'open' a mdb twice. You can 'link' to tables in it but it can never be opened by another user.  
0
 
omgangIT ManagerCommented:
GRayL, maybe I'm misunderstanding your post but an mdb file CAN be opened/accessed from a network location by multiple users at the same time.  This is definitlely not best practice but it is possible.

OM Gang
0
 
omgangIT ManagerCommented:
Not to take away from the good suggestions about splitting the db into front end/back end databasesm, the Q asks specifically about opening a single mdb file by multiple users.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21868449.html?sfQueryTermInfo=1+share

OM Gang
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<When a second computer tries to open the database it refuses to open. >>
How exactly?  You say you get no error messages, but what does it do?
JimD.
0
 
GRayLCommented:
omgang: Right, I stand corrected.  I'm thinking here on my machine, I cannot open the same mdb twice - with the same Access app.  Sorry about that.
0
 
pathsnifferCommented:
I would suggest you have a good think about your application.
Access was designed to a light weight database for desktop application. It a file base database and lacks any network support.

To effectively work around this short comming you will have to compensate it in your application model.

1) Create DSN on each each users machine and connect that to the central Access DB.
2) Use an ADODB type of connection as runtime in the Applicaiton fire a query and close the connection.
3) You will need to handle LOCKS in your application effectively. e.g. For Select queries only use readonly locks etc.

This the best way for you to handle Access DB in a network environment which cause minimum user inconvenience. Also this ensure lower chances of Data corruption, which is big possibility when using Access in a network environment.

Best of Luck
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't agree with creating a DSN on each machine, and while creating an unbound application (and handling the database connections in code) is a good idea, it's beyond the scope of most posters here at EE.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now