Locking tables in MS Access

[Tadej]

I have this a transaction that goes like this:

Dim rst1 As Recordset
Dim rst2 As Recordset
Dim MaxID, i

wspc.BeginTrans

Set rst1 = dbs.OpenRecordset("SELECT Max(ID) FROM Table1")
tbl.MoveFirst
MaxID = tbl.Fields(0)
rst1.Close

Set rst1 = dbs.OpenRecordset("Table1")
Set rst2 = dbs.OpenRecordset("Table2")

For i = 1 To numberOfEntries
rst1.AddNew
rst1.Fields("ID") = MaxID + i
'.
'.
rst1.Update

rst2.AddNew
'.
'.
rst2.Fields("ID_Table1") = MaxID + i
rst2.Update
Next i
rst1.Close
rst2.Close

wspc.CommitTrans

I would like to disable other users to read from or write to both Table1 and Table2 (lock them) while the transaction is active.
If anyone knows how to do this than please answer.

Thanx

Tadej

[JHausmann]

Have you tried pessimistic locking?

[Tadej]

I think that when locking a recordset pessimisticaly
the page containing the record you're editing is
locked as soon as you use the Edit method.
(DAO Reference)

This will not work with my problem, because I'm
adding records.

What I would like to do is lock Table1 and Table2
from BeginTrans to CommitTrans.
Since MaxID is read only once, other users may not
read or add records until the transaction is finished.
MaxID is used as the primary key value in table1 and
as foreign key in table2.

[JHausmann]

If the only method of entering data is programmatically, there's nothing preventing you from coding it yourself.

1) create a "lock" table that has one entry
2) when you want to write to the database, the program attempts to lock the sole record. If it cannot, someone else is uing the database. If it can you proceed on with your work.

The problem is you don't have visibilty to the lock mechanics in Access and that's what you really want.