[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cannot use a microsoft access database with multiple users

Posted on 2008-11-19
11
Medium Priority
?
961 Views
Last Modified: 2012-05-05
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
Comment
Question by:Trevor007
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 200 total points
ID: 23000013
Database property is set to open in Exclusive mode?  Tools --> Options --> Advanced    Check 'Default Open Mode'

OM Gang
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 23000028
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
 

Assisted Solution

by:BIGELOJ
BIGELOJ earned 400 total points
ID: 23000094
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Accepted Solution

by:
zoom2000 earned 1200 total points
ID: 23000676
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
 
LVL 44

Expert Comment

by:GRayL
ID: 23000856
You can never 'open' a mdb twice. You can 'link' to tables in it but it can never be opened by another user.  
0
 
LVL 28

Expert Comment

by:omgang
ID: 23001214
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
 
LVL 28

Expert Comment

by:omgang
ID: 23001236
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
 
LVL 58
ID: 23003387
<<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
 
LVL 44

Expert Comment

by:GRayL
ID: 23005935
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
 
LVL 1

Expert Comment

by:pathsniffer
ID: 23010165
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
 
LVL 85
ID: 23010289
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to fix a SonicWall Gateway Anti-Virus firewall blocking automatic updates to apps like Windows, Adobe, Symantec, etc.
LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question