0

I have a user who created a database using Access 2003.

The problem is, if he's opening the db and made some changes, the other user can open the db but they can't work on it. If he's exited the program, then the user can make some changes.

I would like to know if it's possible for them to work on it at the same time when they open the database?

enter image description here

enter image description here


Additional question:

I tried to do the "Splitting of Database" here and after I clicked on Split I got an error: "The database engine couldn't lock the table, because it is already in use by another person or process"... what does that mean? Did I lock the table?


enter image description here

2 Answers 2

1

Are the users trying to make design changes or just add/edit/delete data? Access doesn't allow more than one user to modify the design of a single object at the same time. If they are jsut trying to edit data then there are a few possabilites.
1) The user has at some point opened the database for exclusive access. you need to get each user of the database to open the database by first opening access, the choosing file->open, browsing to the file, then click the arrow next to the open button and then click open (the top option on the list of 4 that should appear).
2) The form that the users are using may be set to lock the entire table instead of only the current record. If you open the form in design mode there is a property of the form that will tell you how big a lock it takes.

It should not be necessary to split the database into application and data to allow multiple users to edit data at the same time. It is normally a good idea to split the database though.

0

Yes, you can, but typically how this is done is to split the mdb into parts. The tables reside in one .mdb in a shared folder somewhere & the queries, forms & reports live in a client-only version of the database. The client verison contains links to the tables in the 'back-end' database. Use File -> Get External Data -> Link... to connect to the back-end tables.

Each user has a local copy of the 'front-end', but they all share the common 'back-end'.

You could try the simplest thing: Tools -> Options -> Advanced -> Default Open Mode & check that it is set to Shared and that both Open databases using record-level locking and Edited record checked , but my experience just doing this in a multi-user environment is mixed. Sometimes works, sometimes not.

UPDATE

Taking a look at your image: if the form field in question maps to the same database record, then no, you can't have 2 people edit this at the same time. If the field maps to different records, then yes, it's possible.

Yet another update

If you have Access 2003 or similar there is a Database slpitter utility provided. Select Tools -> Database Utilities-> Database Splitter. It will ask for a location to save your files. You'll want this to be a shared drive. It should automatically make the linked tables for you and copy all your table data to a new 'back-end' database named whatever your db name is_be.mdb.

21
  • @DaveParillo: do you mean, the user should have a "back-end database" what about if he doesn't have and he only have a plain ms access 2003? Dec 2, 2009 at 16:06
  • @DaveParillo: i would like to ask the user if he has a back-end database, what is the correct terminology for that? Is this correct: What kind of "back-end database" are you using? thanks Dec 2, 2009 at 16:07
  • The back-end is something you have to create. It involves splitting the existing database into 2 parts, 1 (back-end) only has the tables, 1 (front-end) has everything else. They are both access mdb files. You could ask "Do the tables in the database you're using link to an external datasource?" Dec 2, 2009 at 16:12
  • @DaveParillo: that is already check in this db. not sure what to do next? :-( Dec 2, 2009 at 16:12
  • First step is to determine if Text zu Gesprach 3 maps to completely different records or not when you're having the editing conflict. If your users are trying to edit the same record at the same time, then no, you can't do that in Access. You're done :-( Otherwise you can proceed with splitting the database into parts. Dec 2, 2009 at 16:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .