MS Jet --> MS Access Data Source: How to obtain Update lock

[André Hartmann]

Hi folks,

I have a question how to obtain an update lock when talking to a MS Access
ODBC data source. The DSN is roted to an otherwise unused .mdb file and
driven by the MS Access ODBC driver version 4.00.6200.00 and I have ANSI SQL
switched on (value is set to 1) and I increased the buffer to 102400. All
other settings are default under Windows 2000.

My plan is to issue a statement like

SELECT * from <bla> where <key>=<blabla>

in order to lock the row or rows for an upcoming update. In Oracle I would
do "SELECT .... FOR UPDATE (NOWAIT)", in SQL Server I would do an UPDLOCK
hint. None of those works for my scenario.

What can I do ? Somewhere else on the net someone seriously suggested to
issue a dummy update command like "UPDATE <bla> set <key>=<key> where
<key>=<blabla>" which of course is a nuisance: It'll increase data traffic
and cause unnecessary load on the database. Plus my SQL statements are
created dynamically in the application and then sent to the DSN and I dont
always have the update statement at hand when I want to do the select.. and
it would mean a major redesign in the application to achieve that.

Thanks,

André

 

[Dmitri Ivanov]

Hello Andr?,

AH> Hi folks,
AH>
AH> I have a question how to obtain an update lock when talking to a
AH> MS Access ODBC data source. The DSN is roted to an otherwise unused
AH> .mdb file and driven by the MS Access ODBC driver version
AH> 4.00.6200.00 and I have ANSI SQL switched on (value is set to 1)
AH> and I increased the buffer to 102400. All other settings are
AH> default under Windows 2000.
AH>
AH> My plan is to issue a statement like
AH>
AH> SELECT * from <bla> where <key>=<blabla>
AH>
AH> in order to lock the row or rows for an upcoming update. In
AH> Oracle I would do "SELECT .... FOR UPDATE (NOWAIT)", in SQL Server
AH> I would do an UPDLOCK hint. None of those works for my scenario.

If you do need the lock for updating via a cursor, try to set the
SQL_ATTR_CONCURRENCY statement attribute to SQL_CONCUR_LOCK before calling
SQLExecDirect or SQLExecute.

AH> What can I do ? Somewhere else on the net someone seriously
AH> suggested to issue a dummy update command like "UPDATE <bla> set
AH> <key>=<key> where <key>=<blabla>" which of course is a nuisance:
AH> It'll increase data traffic and cause unnecessary load on the
AH> database. Plus my SQL statements are created dynamically in the
AH> application and then sent to the DSN and I dont always have the
AH> update statement at hand when I want to do the select.. and it
AH> would mean a major redesign in the application to achieve that.

Agree with others that you'd better use an UPDATE statement because nothing
can reduce network trafic while using Access.