How does access work when different users are connected to 1 single table

[pedie]

Hi, how does access work when different users are connected to 1 single table [database in SharedDrive] from a form from their respective computer?

What 'm thinking is....
suppose 3 users log on to same database at a time and opens frontend form does the form [items like textboxes, combos, listbox etc] that are linked to the table linked to database reserve one record for each user so that all users does not update/add new records to the same record/row in the table?

How does it works...just in a layman's term...

 

[xenou]

Access is multi-user by nature and handles such cases more or less automatically - the main thing is that you don't have to worry about it. Generally speaking, adds don't matter much - two users can add to their hearts content. Access doesn't give users a row to work in - it handles this very differently than Excel so don't think in terms of a spreadsheet. Updates (edits) are more vulnerable as it's possible two users could try to edit the same record at the same time.

You can read up on record locking and concurrency which is what this topic generally falls under:
http://msdn.microsoft.com/en-us/library/ms189132.aspx

You will probably want to use the highest level of locking - don't. Unless you really think users will be opening the database to do the same thing at the same time to the same piece of data in the database, you don't need pessimistic locking. You can use optimistic locking. Be optimistic.

Edit:
The above article was for SQL Server but explains the basic concept well.
Here's an MS Acess article:
http://office.microsoft.com/en-us/a...ss-database-on-a-network-mdb-HP005240860.aspx
(see the part about default record locking settings).

Note that "no locks" is what Access uses by default. If a user tries to edit a record another user is using Access will alert the user that they will be overwriting the other persons data and give them a choice. This sounds fine to me. One of the user's must be right and the other wrong, but you'll never know which so you may as well let them fight it out. If you use record locking at the edited records level, its safer but might be youll pay for it with performance and possibly with lockouts. If you use table level locking your users will get many messages that the table is locked - very bad.

You probably don't need to worry about this. As I said, Access doesn't have a row that a user writes data in. It's very different from Excel. You must forget Excel when you work in Access - its a database, not a spreadsheet.

 

[pedie]

Xen, thanks, thank you!!!
"Share only the tables in the Access database" is what 'm going for...
Thanks for clarifying things for me...
So by default it is shared and in my case;
as the users will only be adding data [maybe sometime at the same time] to the database i think there is nothing to worrY about, right? [I know you've said there is notthing to worry about however wanted to confirm again
Assigned Users may edit database sometime but not at the same time...

 

[james_lankford]

Access is multi-user by nature and ...

if there was a sticky for "Tips and Info you should absolutely know" this should definitely be first in the list

that was a great post

my "subscribed to" list is now 719 threads long

 

[pedie]

if there was a sticky for "Tips and Info you should absolutely know" this should definitely be first in the list

that was a great post

my "subscribed to" list is now 719 threads long

James, thanks but didn't quite get/understand what you want to say to me... 'm sure now that my english is not up to the Mark!

Do you want me to check some earlier post regarding the same topic?

 

[Norie]

Pedie

Is it really likely more than one user is going to access the same record at exactly the same time?

 

[pedie]

Norie, more then one user may add new data to one single table...but only one user will be editing the newly added data[by editing data i mean - connect from frontend to database table and make values in record from pending to approve/decline] something like that....

Thanks again everyone!

 

[xenou]

You'll be quite safe then. Access will take care of the messy details of keeping track of the new records as they are added by your users. As far as conflicts editing existing records, it sounds like such conflicts are highly unlikely in this case, and not a worry.

 

[pedie]

You'll be quite safe then. Access will take care of the messy details of keeping track of the new records as they are added by your users. As far as conflicts editing existing records, it sounds like such conflicts are highly unlikely in this case, and not a worry.

Thanks again Xen, you've been really really helpfull