🚀 go-pugleaf

Hi,


It is a Jet limitation. You can reproduce the same problem with the UI, without transaction:

Create t1. Open it in design mode and leave it like it is, in design mode.

Try to create t2 with the reference constraint, you won't be able, since t1 is already open with an
EXCLUSIVE lock, Jet won't be able to open it to check the validity of the implied fields through
the constraint.

In your case, it seems that locking a single record in t1 (through the insert within a transaction)
makes impossible to open the table t1 in an exclusive mode, thing that Jet tries to do, to check
the reference constraint involved in table2 creation:

Put back t1 in edit mode (from the design view we left it), and start to modify an existing record,
BUT DO NOT SAVE the record, leave it dirty, next, try to create t2 (again, without transaction
involved). I used, in the immediate (debug) window, the statement  (it is one line) :

        CurrentDb.Execute "CREATE TABLE t2 (id  AUTOINCREMENT NOT NULL PRIMARY KEY, x  INT NOT NULL
REFERENCES t1(Id), name  VARCHAR(50))"


and I got the same error than you did.

It is definitively a limitation about how Jet works when we create a reference.




Hoping it may help,
Vanderghast, Access MVP


"Plamen Petrov" <plamen.petrov@grutzeck.de> wrote in message news:3cac01a5$1@news.grutzeck.local...
> It's very easy to reproduce the problem.
> Create a table using the next statement:
>
> CREATE TABLE t1
> (
>     id  AUTOINCREMENT NOT NULL PRIMARY KEY,
>     name VARCHAR(50)
> )
>
> After that execute in transaction next 2 statements:
>
> INSERT INTO t1(name) VALUES('abc')
>
> CREATE TABLE t2
> (
>     id  AUTOINCREMENT NOT NULL PRIMARY KEY,
>     x  INT NOT NULL REFERENCES t1(Id),
>     name  VARCHAR(50)
> )
>
> The result is error: 0x80040E14L  -  DB_E_ERRORSINCOMMAND
> State  : 3211
> Native : -534316312
>
> The database engine could not lock table 't1' because it is already
> in use by another person or process.
>
> The same code works with SQL Server. Is it a bug in Jet,
> or I'm not doing it in the correct way?
>
>
>
>
>