Error 3346, Trying To Insert Into, Two Consecutive Tables

I have those two tables is MS Access : lkpSchemaPIT : | UID | lkpSchemaTitleEng | |-----|--------------------------| |--1--|---------Title1-----------| |--2--|--------

Solution 1:

Once again, here is an example where parameterized queries would be invaluable as you avoid quote enclosures (including # for date/times) and string concatenation that conflates data and SQL together, rendering hard to read and maintain code.

MS Access allows the PARAMETERS clause where you can define the parameter placeholder names and data types and then in VBA you can bind values to such parameters using QueryDefs. No quotes needed or string interpolation.

SQL(save both as stored queries)

PARAMETERS TitleParam Text(255);
INSERTINTO lkpSchemaPIT (lkpSchemaTitleEng)
VALUES (TitleParam);

PARAMETERS UIDParam Long, PUIDParam Long, SortParam Long, 
           LevelParam Long, DateStart Datetime;
INSERTINTO lkpSchemaPITChronology (UID, PUID, [Sort], [Level], DateStart)
VALUES (UIDParam, PUIDParam, SortParam, LevelParam, DateStartParam);

VBA

...
Dim qdef As QueryDef

With CurrentDb

   ' FIRST QUERYSet qdef = .QueryDefs("myFirstSavedAppendQuery")

   qdef!TitleParam = [Title]
   qdef.Execute dbFailOnError

   ' SECOND QUERYSet qdef = .QueryDefs("mySecondSavedAppendQuery")

   qdef!UIDParam = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
   qdef!PUIDParam = [ParentID]
   qdef!SortParam = [NewSort]
   qdef!LevelParam = [Level]
   qdef!DateStart = Date()

   qdef.Execute dbFailOnError

EndWithSet qdef = Nothing