Solved Insert into hanging (1 Viewer)

why

Member
Local time
Today, 09:13
Joined
Sep 28, 2020
Messages
40
I am trying to run an insert into from one table to the second table. For some reason, I think it does not like the "not in". Here is the code. It does not give an error it just spins Currently there are about 40 records it is trying to insert. I also tested with just a select statement and it does the same thing.

Code:
Dim db As Database
Set db = CurrentDb

db.Execute "INSERT INTO all_matters (RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD) " & _
" SELECT RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD " & _
" FROM all_matters1 WHERE all_matters1.RowID NOT IN (SELECT all_matters.RowID FROM all_matters); "
 

Isaac

Lifelong Learner
Local time
Today, 07:13
Joined
Mar 14, 2017
Messages
3,218
What happens if you change it to this and run

Code:
Dim db As Database
Set db = CurrentDb

db.Execute "INSERT INTO all_matters (RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD) " & _
" SELECT RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD " & _
" FROM all_matters1 WHERE all_matters1.RowID NOT IN (SELECT all_matters.RowID FROM all_matters); ",dbfailonerror
 

why

Member
Local time
Today, 09:13
Joined
Sep 28, 2020
Messages
40
What happens if you change it to this and run

Code:
Dim db As Database
Set db = CurrentDb

db.Execute "INSERT INTO all_matters (RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD) " & _
" SELECT RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD " & _
" FROM all_matters1 WHERE all_matters1.RowID NOT IN (SELECT all_matters.RowID FROM all_matters); ",dbfailonerror
So far it has not errored out. I let it run for 20 minutes. It just shows a spinning circle. I am going to kill it and try removing fields.
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 02:13
Joined
Jul 4, 2013
Messages
2,456
If the field name is called Billing/Attorney, put square brackets around ie
[Billing/Attorney]
 

why

Member
Local time
Today, 09:13
Joined
Sep 28, 2020
Messages
40
Thanks everyone I got it working not sure why but a left outer join works where the not in does not

Code:
Private Sub all_mattersinsert()
Dim db As Database
Set db = CurrentDb

db.Execute "INSERT INTO all_matters (RowID, Client, Matter, ClientName, MatterDescription, OpenedDate, ClosedDate, OriginatingAttorney, BillingAttorney, MatterType, ClosedFile1, ClosedFile2, Years, Notes, DestructionDate, ProposedDestructionD) " & _
" SELECT all_matters1.RowID, all_matters1.Client, all_matters1.Matter, all_matters1.ClientName, all_matters1.MatterDescription, all_matters1.OpenedDate, all_matters1.ClosedDate, all_matters1.OriginatingAttorney, all_matters1.BillingAttorney, all_matters1.MatterType, all_matters1.ClosedFile1, all_matters1.ClosedFile2, all_matters1.Years, all_matters1.Notes, all_matters1.DestructionDate, all_matters1.ProposedDestructionD " & _
" FROM all_matters1 " & _
" Left JOIN all_matters ON all_matters.RowID = all_matters1.RowID  Where all_matters.RowID IS Null; "
End Sub
 

Users who are viewing this thread

Top Bottom