🚀 go-pugleaf

Thanks

    It's enough to know that I can't do any more. I'm going to move the
creation of the referential constraint at the end of the batch, after the
transaction. This is an approximate solution, but it's enough for now.

Plamen



"Michel Walsh" <Vanderghast@msn.com> wrote in message
news:O$0RX882BHA.2848@tkmsftngp05...
> 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?
> >
> >
> >
> >
> >
>
>