JET OLEDB & Access-MDB in multi-user environment

[sigiK]

i have a MDB in a multi-user-environment (5 user) and use OLEDB to manipulate data.

in some situations (unpredictable), a user gets a
3218 ("Update not possible, currently locked")
from the JET-Engine when executing e.g. an INSERT-command

til now, i used the connection string

gCnnM.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DataBase & ";"

without further settings.

could it be, that there is a default of "Page-Level Locking" for the property Jet OLEDB : Database Locking Mode ?

or has anyone an idea, where my problem could be ?

generally: where can i find the default values for the ADO-properties when connecting to an Access-Databse ?

THX for help !!

[Ecniv]

Generally when a record is being entered/editted it used to lock 2kb on either side of the record. If you built the db to be bound then when you edit this may happen.

If you use inserts and there is no bound editting, its just bad luck. In which case catch the error, and try it again after 2 seconds. If that failas ask the user... ?

[sigiK]

... If you built the db to be bound then when you edit this may happen.

If you use inserts and there is no bound editting, its just bad luck. In which case catch the error, and try it again after 2 seconds. If that failas ask the user... ?

wow, thank u for quick reply ... but i am confused...
what does "built the db to be bound" mean ??
it is a "normal" mdb-file containing tables (and only tables)

is catching the error and retry it after a while the only way to avoid such situations ?

sigi

[GaryMazzone]

Does someone have a record set open? What is the mode of the recordset? When you open a recordset in Access the database will lock that record so that only the person having the record open first can edit it (this prevents some from over writing changes you make). What Access does is something called page locking (instead of record locking). A page lock will lock any record that exsists in the same 2K block as the record you are working on. So if the record is less then 2K in length you might be locking 2 or more records at the same time. If the recordset was opened as open table you might actually be locking the full table from any insert,update or delete from anyone but that person who opens the table first.

[sigiK]

i use the follwoing code for READING and filling data into a datatable:
(up to max 500 records)

          Dim cmD As System.Data.OleDb.OleDbCommand
            Dim cdR As System.Data.OleDb.OleDbDataReader
            Dim xSQL As String

            xSQL = "SELECT " & gSQLlimit & " * FROM ADRESSE " 
            cmD = New System.Data.OleDb.OleDbCommand(xSQL, gCnn)
            cdR = cmD.ExecuteReader
            While cdR.Read
                        ' **** fill data into datatable
            End While
            cdR.Close()

for all other functions (INSERT, UPDATE, DELETE) - i use code like this (creating an SQL-string and executing with .ExecuteNonQuery

        Dim cmD As System.Data.OleDb.OleDbCommand
        Dim xSQL as String
        On Error GoTo DErr
        xSQL = "DELETE FROM ADRESSE WHERE ADadnr = " & mADRESSE.ADadnr
        cmD = New System.Data.OleDb.OleDbCommand(xSQL, gCnn)
        cmD.ExecuteNonQuery()

so i think, a user has only for a very short moment an open recordset.

there is no need of locking when i use the recordset - how could i turn off locking ???

THANK YOU !

[randem]

This could possibly give you some assistance - Database Problems and pay close attention to Concurrency

[sigiK]

nice, but no information, how to run a select-command without LOCK ..
my program should work exaktly like described in the article above -
but to achieve this, i should be able to run a .ExecuteReader-Command
without producing a lock ...

[sigiK]

can really no one give me a tip ????

[randem]

Selects don't produce locks if you don't ask them to...

[sigiK]

but from where could my lock-situation come ?
all INSERT/UPDATE/DELETE refer to one single ROW ?

[randem]

Are you opening the select recordset with a forward only (ReadOnly) Cursor? That would solve your problem.

[sigiK]

no, not so far as i know - pls look at the code i posted in this thread.
how can i set this option ?

[randem]

Sorry, I don't use .NET but the process is still the same. You MUST indicate to use a different type of cursor than the default. The default cursor is not always the correct cursor to use especially in a multi-user environment.

[sigiK]

Sorry, I don't use .NET but the process is still the same. You MUST indicate to use a different type of cursor than the default. The default cursor is not always the correct cursor to use especially in a multi-user environment.

perhaps you could show me, how YOU do that ? (.net or not) -
then i could transform it to my need ?

[randem]

    snpTable.Open SQL, RSD.dbCurrent, adOpenStatic, adLockReadOnly, adCmdText

    snpTable.Open SQL, RSD.dbCurrent, adOpenForwardOnly, adLockReadOnly, adCmdText