Simultanious Insert.

[swapna_munukoti]

Hi all,

I was trying to insert few records(approximately 1000) into access
DB from two different machines simultaneously using insert query. I am
using Begin and commit transaction. After every 100 insert statements,
I am displaying a message box.
I started the application in one machine first and waited at the
message box after executing 100 insert statements. And then started the
application in another machine. The application in another machine got
hanged till the transaction in the 1st machine is completed.
It didn't even throw an error. Can anyone explain me why did it
happen.
I want to know if MS-Access allows multiusers to insert the records
to same table simultaneously. If not, then is there a way to achieve
this?

Thanks,
Swapna.

 

[Guest]

It is entirely controlled by your lock settings. Most likely you have Table
Level Locking set. However, Access does support going to Row Level.

HTH

Sharkbyte

 

[John Spencer (MVP)]

If my understanding is correct, when you begin a transaction it looks the table
until you commit the transaction. So if you begin transaction on computer A the
table is not available until you commit (or rollback) the transaction.

The behavior you are seeing is the behavior I would expect to see - it is not an
error, so why would you get an error message.

 

[swapna_munukoti]

The 2nd user don't know why the application is suddenly hanging
like this right? He should be given some message saying he has to wait
for time or some other appropriate message. Thatz why I am expecting
the DB to throw some error that the table is locked or so.

 

[swapna_munukoti]

I found recently MS-Access behaving in a different manner. I don't know
the reason.
In the same scenario which I have mentioned previously (While
inserting records to MS-Access simultaneously by different users to the
same table), it is throwing an error stating "Could not update;
Currently locked".
Does anybody know the reason why it is behaving differently in the
same scenario?