Help: Is there any ODBC-driver for Access that supports locking? 
Author Message
 Help: Is there any ODBC-driver for Access that supports locking?

Hello.

Is there any ODBC driver for Microsoft Access 7.00 or newer that
supports optimistic or pessimistic locking of a page/record?

I'm using the CDatabase/CRecordset class in the MFC-library to connect
to an Access 7.0 database.
The ODBC-driver I'm using to connect to the Access database is the one
that ships with Visual C++ 5.0.

Using CRecordset::SetLockingMode() to set pessimistic locking throws an
exception "Locking mode not supported".

If optimistic locking mode is used and a record is updated by two users
at the same time, the last user will overwrite
the first users changes without any info that the record has changed. So
there seems to be no locking at all!

Thanks for any comments or help.


Mikael N?ssn



Fri, 02 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:

>If optimistic locking mode is used and a record is updated by two users
>at the same time, the last user will overwrite
>the first users changes without any info that the record has changed. So
>there seems to be no locking at all!

Do you use transactions? Optimistic locking does not work in autocommit
mode.


Sat, 03 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:
>If optimistic locking mode is used and a record is updated by two users
>at the same time, the last user will overwrite
>the first users changes without any info that the record has changed. So
>there seems to be no locking at all!

I agree, from my tests I see no way of locking, because it must be locked as
it's read, before update is called. Well, after its read, its too late.

Please let me know what you find. I'm going to have to lock out the ENTIRE
table with a mutex.

Brian



Sat, 03 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:

> >If optimistic locking mode is used and a record is updated by two
> users
> >at the same time, the last user will overwrite
> >the first users changes without any info that the record has changed.
> So
> >there seems to be no locking at all!

> Do you use transactions? Optimistic locking does not work in
> autocommit
> mode.

I use transactions for some operations by calling CDatabase:s member
functions BeginTrans, CommitTrans and Rollback.But optimistic locking
does not seem to work for any operation, it does not matter if I use
transactions or not.

Have you got optimistic locking to work on an Access database? Are you
using the MFC classes for ODBC?

What was the result if two users updated the same record at the same
time, did the last update fail?

/Mikael N?ssn



Sat, 03 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:
>Have you got optimistic locking to work on an Access database? Are you
>using the MFC classes for ODBC?

>What was the result if two users updated the same record at the same
>time, did the last update fail?

>/Mikael N?ssn

I am using CDatabase but not CRecordset, that class has caused me too much
trouble already. I have used the following connection options:

A keyset driven cursor without the cursor library (dynaset).
SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN

Optimistic concurrency.
SQL_ATTR_CONCURRENCY, SQL_CONCUR_VALUES

Transaction isolation level read committed (default).
SQL_ATTR_TXN_ISOLATION, SQL_TXN_READ_COMMITTED

And Transactions.
SQL_ATTR_AUTOCOMMIT,  SQL_AUTOCOMMIT_OFF

With that I can see the expected behaviour. A change made by the first
transaction is not seen by the second until committed and a attempt to
change a field that has already been changed by the first transaction fails
with the following error message:

[Microsoft][ODBC Microsoft Access 97 Driver] Couldn't update; currently
locked by user 'admin' on machine 'LGSRV'.



Sun, 04 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:
> With that I can see the expected behaviour. A change made by the first

> transaction is not seen by the second until committed and a attempt to

> change a field that has already been changed by the first transaction
> fails
> with the following error message:

> [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't update;
> currently
> locked by user 'admin' on machine 'LGSRV'.

I get the same message when using transactions, the second transaction
tries to
update the record for about 2 seconds and then gives up showing the
message.

The problem is that the message only shows while the first transaction
is active
(i.e. CommitTrans() or Rollback() has not been executed).
So if the first transaction has commited the transaction before the 2
second delay
the second transaction will overwrite the first transactions update
without any message.

Also when not using transactions there is no message that the record has
changed.

/Mikael N?ssn



Sun, 04 Feb 2001 03:00:00 GMT  
 Help: Is there any ODBC-driver for Access that supports locking?

Quote:
>I get the same message when using transactions, the second transaction
>tries to
>update the record for about 2 seconds and then gives up showing the
>message.

>The problem is that the message only shows while the first transaction
>is active
>(i.e. CommitTrans() or Rollback() has not been executed).
>So if the first transaction has commited the transaction before the 2
>second delay
>the second transaction will overwrite the first transactions update
>without any message.

>Also when not using transactions there is no message that the record has
>changed.

I have tried this and youre right, concurrency control is broken in the
access driver although I recall that it did work with older versions. Sorry
for the bad news.


Mon, 05 Feb 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. MS Access 2.0 ODBC driver does not support transactions

2. ODBC Access driver on peer-to-peer help

3. Oracle ODBC driver instead of Microsofts ODBC Oracle driver

4. "ODBC driver does not support dynasets"

5. Oracle ODBC Drivers that support CRecordset::dynaset

6. Oracle ODBC Drivers that support Dynasets?

7. ODBC Driver does not support Dynasets?

8. Does ODBC text driver support UNICODE?

9. Oracle ODBC driver : SQLExtendedFetch() is not supported ?

10. ODBC driver does not support dynasets

11. ODBC driver does noet support dynasets....

12. SQL procedure calls supported by JET ODBC driver

 

 
Powered by phpBB® Forum Software