Append Query Locks my Database!

[Eka1618]

Hello,

I am having a problem with my database going into a total lock down after I
run an append query (qryAppendTests). It's sort of like the program is
freezing, but it is not freezing, It is just locking up.

I have read some other threads about using a UNION to append records from
multiple tables. I do not think this will work in my case. I have 2 tables
(tblRequest and tblTest) that have specific fields that I need to combine
together to formulate this new record in tblQueue. tblQueue also has other
fields in a record that the user will fill in later. During the append query,
only part of a record added to tblQueue is appended.

Has anyone ever encountered this problem? I do not know what else to do at
this point. I have recreated my entire project twice now in different ways to
try and solve this problem. The reason I do not understand what is happening
is because I do not get any errors. The query actually works the way I want
it to, minus the fact that my database lock up after I run it...

Also, the .ldb file stays on the screen after I close the program throug the
task manager (which is the only way I can close access after runing the
query).

Here is the SQL for my query:

INSERT INTO tblQueue ( PART_NO, REQUESTOR, DUE_DATE, TEST_TYPE, CYCLE_NO,
TEST_ID )
SELECT tblTest.PART_NO, tblRequest.EMP_ID, tblRequest.DUE_DATE,
tblTest.TEST_TYPE, tblTest.CYCLE_NO, tblTest.TEST_ID
FROM (tblRequest INNER JOIN tblTest ON tblRequest.PART_NO = tblTest.PART_NO)
INNER JOIN tblQueue ON tblTest.TEST_ID = tblQueue.TEST_ID
WHERE (((tblTest.PART_NO) Like [forms].[frmPartNumber].[cmbPart]));


If anyone has any suggestions for me, please let me know. Thank you!

~Erica~

 

[Jerry Whittle]

Hi,

Thanks for posting the SQL statement. It helps.

You are doing an INNER JOIN on tblQueue which is also the table that you are
inserting into. That can lead to something like what is called a dead lock in
some other databases. Or it could just be going in circles as you are
checking for a join on a record that you just inserted.

The way out of it is to rewrite the inner join into a subquery using
something like an IN statement or possibly EXISTS. Below is a very simple
example of an Insert with an IN clause.

INSERT INTO T1 (Field1)
SELECT T2.[Field1]
FROM T2
WHERE T@.[Field1] In (Select Field1 from T1) ;