Can MS Access 2007 support multiuser environment?

2

There is an application which is running on several machines(say roughly on 2).This application updates an shared mdb placed on network.Both users are trying to update the shared mdb at one time but the problem is only one user is able to update mdb at one time.Another user is not able to open it.Can anyone suggest that access support multiuser environment?

edit:
There is one form TFormRoadAttrib.When it activates following function is called

procedure TFrmRoadAttrib.FormActivate(Sender: TObject);
if dmTimeDomain <> nil then
   begin
     if not (dmTimeDomain.dbTimeDomain.InTransaction) then
     begin
       dmTimeDomain.dbTimeDomain.BeginTrans;
     end;
   end;

where dbTimeDomain=TADOConnection and its value is 

'Provider=Microsoft.ACE.OLEDB.12.0;
Mode=Share Deny None;
Extended Properties="";
Locale Identifier=1033;
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=4;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Data Source=Q:\BEL_01\BEL_GADM\ACCESS\Restrictions.mdb;
Jet OLEDB:System database=C:\Program Files\Tele Atlas\Common Files\DPT.MDW;
User ID=dbadpt;
Password=dbadpt;

When we click on Ok button following code executes

if dmTimeDomain <> nil then
 begin
      if (dmTimeDomain.dbTimeDomain.InTransaction) then
            dmTimeDomain.dbTimeDomain.CommitTrans;
     end;
end;

Kindly suggest.

anshul

출처 delphi ms-access
  • Remove the delphi tag. It is not applicable or helpful to your quesion. - Steve Jorgensen
  • What if one of the users is using a Delphi application? There isn't enough information provided to know, but it's possible something in the Delphi data access layer might be causing the problem. Actually, now that I've read the comments below, this is precisely the case. The Delphi tag definitely belongs, but we also need the Delphi code so Delphi folks can figure out what the options are. - David-W-Fenton
  • Please post the Delphi code that controls the transaction. That's likely the source of the problem. - David-W-Fenton
  • I have updated the description field of my question Kindly have a look on that for additional info. - anshul
  • Connection mode defined in the connection string is "Share Deny None" while in shared mdb it is set to "share".If i am changing the mode in connection string to "ReadWrite" then error is coming. - anshul

3 답변

4

Access definitely supports a multi-user environment, but your permissions must be set correctly. All users must be able to create files in the directory where the database is located, and all users must have permission to modify files created in that directory by other users. There are many ways to mess that up. This is because Access uses a separate .ldb file as part of its mechanism for managing concurrent, multi-user access.

A good test is to have one user create a text file in the shared directory, and then make sure the other user can open that file, and then save a change to it.

  • Thanks Steve.But this is not the problem.Every user has full access rights on network drive.If one user closes mdb then another is able to open and update it. - anshul
  • Are both users opening the database directly, or via linked tables? If directly, then make sure neither user is attempting to open the database for "exclusive access. Also, permission settings can be complicated, so I do recommend doing the test I suggested. It is possible for users to have full access to a directory, but for new files created by one user in the directory to be unmodifiable by other users. - Steve Jorgensen
  • This may be of interest: office.microsoft.com/en-us/access-help/…. Is it possible that the applications allows modification of objects in the back-end database? - Fionnuala
  • It may be worth testing with a 'clean' front-end, that is an Access front-end with a simple form and one or two linked tables. Let each user have a copy and see if the problem still exists. - Fionnuala
  • I think i have found something on this.Whenever an user access the shared mdb a transaction begins and until this transaction commits no other user can update shared mdb because it get locked for that duration.The application is in Delphi which is using inittrans,committrans etc.I think problem resides here.Is there any way to know when this transaction will complete or when mdb get unlocked.Note:All users are using there individual application running the only thing which is common in them is shared mdb only. - anshul
1

Both should be able to use the app. If one user is editing a form or table, the others are locked out of editing those same objects. But that should have no bearing on the app once it's in "production" state. A few years ago I helped convert a large app to MS SQL Server backend (stil MS Access frontend) and until that point, they had been successfully using the app with 15 users simultaneously. The app just got too big (100 forms, 100 tables, some with a million rows) so they moved for performance reasons. Otherwise they'd still be totally on Access.

0

Consider using an Access Project (adp extension) instead of a normal access mdb file. An access project works with SQL server desktop engine which you will find as a separate install file on you Office CD. This essentially means you have a slightly watered down version of SQL server running and this server will take care of all your concurrency issues for you. Also, if your DB becomes too big for an Access Project, you can easily port your DB to a fully fledged SQL Server machine. You can do most of the things in an Access Project that you can do with SQL Server, including Stored Procedures etc, and it's pretty easy to setup and connect to.

  • Comments on Steve Jorgensen's answer show that the front end is not Access, but a Delphi app, so this is an inapplicable suggestion. - David-W-Fenton
  • Also, I have written 2 applications as ADPs, and I do not recommend it. ADPs create many more problems than they solve. - Steve Jorgensen

Linked

Related

Latest