Jet database engine and table LOCKS

[leon]

Hi all,

I have an .mdb file (access database) which has been set to table level
locking (i.e. not just records in question, but the whole table). The
database is also in sharing mode (ie. not exclusive)...

here is my problem:

i have an application that accesses the aforementioned database from Jet
ODBC driver and runs a query which implicitly locks the whole table -
that is just what i want. The results are very good when trying to open
the same table from Access software - the table is told to be locked
(just what I want) so all appears to be going ok....

HOWEVER, when reverse is applied - that is when I open the table from
Access software, and then try to run the application which contacts the
databse from ODBC - the application goes ahead (albeit a bit slower) and
sees the table - when I really want it to tell me that the table is
locked!!!

Further, when I just run two instances of the application which accesses
the database from ODBC driver - both can access the table at the same time.

The queries and type of ODBC connections initiated by applications are
for updates (not read-only) - in fact this is implictly indicated when
MS Access software when it tells me that the table is locked (if i try
to open it from MS Access)...

the LDB file is present by the way...

So my thinking is - perhaps Jet engine whilst correctly setting the list
of connections to table in ldb file does not actually honour the locking
policy (i.e. ignores it) and lets the ODBC connection still deal and
look at the data? if so - is anyone out there who is aware of latest
(and hopefully greatest) version of Jet driver and if there were any
bugs in relation to the aforementione issues with Jet driver?

Kind regards,
Leon.

PS I realise that table locking is bad, slow , etc., etc. - I still have
to use it though so whilst "why do you want to do it in the first
place?" questions are welcome they will be of little value in this context.

[Tom van Stiphout]

On Tue, 29 Mar 2005 17:11:33 +0000, leon <sv*@iprimus.co m.au> wrote:

Why do you want to do this :-)

I don't think you can have this fine control over Jet's locking
strategy. You would rather have to create your own pseudo-lock table
and use this information. For example that table could hold the
computer names and a YesNo field. When a computer triesto access the
table, it should first check with this table if YesNo not set for any
other machine. Then it should set it, do its thing with the table, and
unset it.

-Tom.

Hi all,

I have an .mdb file (access database) which has been set to table level
locking (i.e. not just records in question, but the whole table). The
database is also in sharing mode (ie. not exclusive)...

here is my problem:

i have an application that accesses the aforementioned database from Jet
ODBC driver and runs a query which implicitly locks the whole table -
that is just what i want. The results are very good when trying to open
the same table from Access software - the table is told to be locked
(just what I want) so all appears to be going ok....

HOWEVER, when reverse is applied - that is when I open the table from
Access software, and then try to run the application which contacts the
databse from ODBC - the application goes ahead (albeit a bit slower) and
sees the table - when I really want it to tell me that the table is
locked!!!

Further, when I just run two instances of the application which accesses
the database from ODBC driver - both can access the table at the same time.

The queries and type of ODBC connections initiated by applications are
for updates (not read-only) - in fact this is implictly indicated when
MS Access software when it tells me that the table is locked (if i try
to open it from MS Access)...

the LDB file is present by the way...

So my thinking is - perhaps Jet engine whilst correctly setting the list
of connections to table in ldb file does not actually honour the locking
policy (i.e. ignores it) and lets the ODBC connection still deal and
look at the data? if so - is anyone out there who is aware of latest
(and hopefully greatest) version of Jet driver and if there were any
bugs in relation to the aforementione issues with Jet driver?

Kind regards,
Leon.

PS I realise that table locking is bad, slow , etc., etc. - I still have
to use it though so whilst "why do you want to do it in the first
place?" questions are welcome they will be of little value in this context.