错误3346，试图将INERT插入两个连续的表 [英] Error 3346, trying to INSERT INTO, two consecutive tables

问题描述

我有两个表是MS Access：

lkpSchemaPIT：

  | UID | lkpSchemaTitleEng | 
 | ----- | -------------------------- | 
 | --1-- | ---------标题1 ----------- | 
 | --2-- | ---------标题2 ----------- | 
  

...

lkpSchemaPITChronology：

  | ID | UID | PUID |排序|级别| DateStart | DateEnd | 
 | ---- | ----- | ------ | ---------- | ------- | ----------- | --------- | 
 | --0- | --1-- | --0 --- || --- 5-- | --2 ---- | --- Now（）--- |- NULL --- | 
  

...

第一张桌子仅包含我要在访问中放入树视图的节点。我使用第二张表来构建树，但也要跟踪节点多年来可能拥有的所有父节点。您可以看到两个表中的UID是相同的，但是它们之间没有关系，当我构建树时，我使用带有联接的查询。

我的问题是：当我想在lkpSchemaPIT表中添加新节点时，我还需要能够添加其树视图信息（父项，排序，级别等）。

这是我到目前为止的代码：

  With CurrentDb 
 .Execute _ 
 INSERT INTO lkpSchemaPIT& _ 
（lkpSchemaTitleEng）& _ 
 VALUES& _ _ 
（''& Title&'） 
 .Execute _ 
 INSERT INTO lkpSchemaPITChronology VALUES（& .OpenRecordset（ SELECT @@ IDENTITY）。Fields （0）&，& [ParentID]&，& [NewSort]&，& [Level]&，& Date&，null） 
 
结尾为
  

ParentID，NewSort，Level是之前确定的3个变量我叫这一切。
日期参数是返回当前日期的VBA函数。

我知道第一个INSERT INTO正在工作，因为在表中显示了一个新值。但是第二次INSERT INTO不起作用，我能够得到错误：

错误3346-查询值和目标字段的数量不一样。

有人遇到过这种问题吗？

解决方案

再一次，这是一个示例，其中参数化查询将非常有用，因为您可以避免使用引号括起来（包括日期/时间的＃）和字符串连接，

MS Access允许使用 PARAMETERS 子句，将数据和SQL放在一起，使其难以阅读和维护代码。可以定义参数占位符名称和数据类型，然后在VBA中，您可以使用 QueryDefs 。不需要引号或字符串插值。

SQL （均保存为存储查询）

  PARAMETERS TitleParam Text（255）; 
插入lkpSchemaPIT（lkpSchemaTitleEng）
值（TitleParam）; 
 
参数UIDParam Long，PUIDParam Long，SortParam Long，
 LevelParam Long，DateStart Datetime; 
插入lkpSchemaPITChronology（UID，PUID，[Sort]，[Level]，DateStart）
值（UIDParam，PUIDParam，SortParam，LevelParam，DateStartParam）; 
  

VBA

< pre class = lang-vb prettyprint-override> ...
Dim qdef作为QueryDef

与CurrentDb

'优先查询
设置qdef = .QueryDefs（ myFirstSavedAppendQuery）

qdef！TitleParam = [标题]
qdef。执行dbFailOnError

'第二查询
Set qdef = .QueryDefs（ mySecondSavedAppendQuery）

qdef！UIDParam = .OpenRecordset（ SELECT @@ IDENTITY）。Fields（0）
qdef！PUIDParam = [ParentID]
qdef！SortParam = [NewSort]
qdef！LevelParam = [Level]
qdef！DateStart = Date（）

qdef。执行dbFailOnError

以

结尾qdef =无


I have those two tables is MS Access :

lkpSchemaPIT :

| UID |    lkpSchemaTitleEng     |  
|-----|--------------------------|  
|--1--|---------Title1-----------|  
|--2--|---------Title2-----------|  

...

lkpSchemaPITChronology :

| ID | UID | PUID | Sort | Level | DateStart | DateEnd |  
|----|-----|------|------|-------|-----------|---------|  
|--0-|--1--|--0---|---5--|--2----|---Now()---|--NULL---|  

...

The first table contains just nodes that i'm going to put in a treeview in access. I use the second table to construct the tree, but also keep track of all the parent that a node could've had through the years. You can see that the UID in the two tables are the same, but they have not a relationship between them, when I build the tree, I use a query with a join on it.

My problem is : When I want to add a new node in the lkpSchemaPIT table, I need to be able to add its "treeview" info as well (Parent, Sort, Level, etc.).
This is my code so far :

With CurrentDb
      .Execute _
        "INSERT INTO lkpSchemaPIT " & _
          "(lkpSchemaTitleEng) " & _
        "VALUES " & _
          "('" & Title & "')"
      .Execute _
        "INSERT INTO lkpSchemaPITChronology VALUES (" & .OpenRecordset("SELECT @@IDENTITY").Fields(0) & ", " & [ParentID] & ", " & [NewSort] & ", " & [Level] & ", " & Date & ", null)"

End With  

ParentID, NewSort, Level are 3 variables that have been determined before I call all this. The "Date" parameters is the VBA function that returns the current date.

I know that the first INSERT INTO is working because a new value is displayed in my table. But the second INSERT INTO isn't working and I was able to get the error :

Error 3346 - Number of query values and destination fields are not the same.

Is anyone ever had this kind of problem ?

解决方案

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);
INSERT INTO lkpSchemaPIT (lkpSchemaTitleEng)
VALUES (TitleParam);

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

VBA

...
Dim qdef As QueryDef

With CurrentDb

   ' FIRST QUERY
   Set qdef = .QueryDefs("myFirstSavedAppendQuery")

   qdef!TitleParam = [Title]
   qdef.Execute dbFailOnError

   ' SECOND QUERY
   Set 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

End With

Set qdef = Nothing

这篇关于错误3346，试图将INERT插入两个连续的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！