Problem with MS Jet 4.0

Thread View: comp.databases.ms-access

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Plamen Petrov Thu, 04 Apr 2002 07:32

#272

Author: Plamen Petrov
Date: Thu, 04 Apr 2002 07:32

29 lines
2273 bytes

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?

Re: Problem with MS Jet 4.0

#273

Author: Michel Walsh
Date: Thu, 04 Apr 2002 11:53

69 lines
3572 bytes

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?
>
>
>
>
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads