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

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é

Hello Andr?,
"Andr? Hartmann" wrote:

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.
--
Sincerely,
Dmitri Ivanov
Common Lisp ODBC interface - www.ystok.ru