SQL从VBA插入到Access数据库 [英] SQL Insert from VBA to Access Database
问题描述
您好。我在论坛上的第一篇文章,我希望有人可以帮助解决这个问题。我有一张叫做''库存''的桌子。它有11个领域,其中两个是''Num''和''Plant''。这些工厂中有许多植物和相应的数字范围:
植物编号
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
等
因此,对于TBM,它可能有四个现有记录,1000,1001,1002,1003
要插入TBM的下一条记录,我必须知道下一个数字
值,这将是1004.如果我从VBA表格中设置了
表库存中的字段的所有值,我可以使用以下代码插入记录。 (var1对应于表单中的'Number''值,var2表示来自表单的''Plant''值。
appAccess.DoCmd.RunSQL _
"(插入库存值(''" _
& var1&"'',''" _
& var2&"'',''" _
& var3&"'',''" _
& var4&" ;'',''" _
& var5&"'',''" _
& var6&"'', ''" _
& var7&"'',''" _
& var8&"'',''" _
& var9&"'',''" _
& var10&"'',''" _
& var11&"''))"
但是,我想要做的是创建一个将计算下一个的插入
可用数量基于工厂价值,在c TBM的一个,1004。我有
尝试以下但它似乎没有工作,虽然嵌套的选择
自己工作正常。任何想法。
appAccess.DoCmd.RunSQL _
"(插入库存值(''(_
select)最大(数字)+ 1来自库存,其中Plant ='"& var2&"`)'',''" _
& var2&"'',' '" _
& var3&"'',''" _
& var4&"'',''" _
& var5&"'',''" _
& var6&"'',''" _
& var7&"'',''" _
& var8&"'',''" _
& var9&"'',''" _
& var10&"'',''" _
& var11& ;'''')"
所有代码均为excel的VBA 6.3,数据库为Access 2003 SP2。如果您需要更多代码,请告诉我。
非常感谢提前。
Martin。
Hi. My first post on the forum and I hope someone can help with this problem. I have a table called ''Inventory''. It has 11 fields, two of them being ''Num'' and ''Plant''. There are a number of ''Plants'' and the corresponding number ranges within those plants are:
Plant Number
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
etc.
So for TBM for example, it may have four existing records, 1000, 1001, 1002, 1003
To insert the next record for TBM, I would have to know the next Number
value, which would be 1004. If I set all the values for the fields in the
table Inventory from a VBA form manulally, I can use the following code to Insert the record. (var1 corresponds to the ''Number'' value from the form and var2 the ''Plant'' value from the form).
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (''" _
& var1 & "'', ''" _
& var2 & "'', ''" _
& var3 & "'', ''" _
& var4 & "'', ''" _
& var5 & "'', ''" _
& var6 & "'', ''" _
& var7 & "'', ''" _
& var8 & "'', ''" _
& var9 & "'', ''" _
& var10 & "'', ''" _
& var11 & "''))"
However, what I want to do is create an Insert that will calculate the next
available Number based on the Plant value, in the case of TBM, 1004. I have
tried the following but it doesn''t seem to work, although the nested select
works fine on its own. Any ideas.
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (''( _
select max (Number) + 1 from Inventory where Plant = `" & var2 & "`)'', ''" _
& var2 & "'', ''" _
& var3 & "'', ''" _
& var4 & "'', ''" _
& var5 & "'', ''" _
& var6 & "'', ''" _
& var7 & "'', ''" _
& var8 & "'', ''" _
& var9 & "'', ''" _
& var10 & "'', ''" _
& var11 & "''))"
All code is VBA 6.3 from excel and the database is Access 2003 SP2. If you need anymore code, let me know.
Many thanks in advance.
Martin.
推荐答案
嗨。我在论坛上的第一篇文章,我希望有人可以帮助解决这个问题。我有一张叫做''库存''的桌子。它有11个领域,其中两个是''Num''和''Plant''。这些工厂中有许多植物和相应的数字范围:
植物编号
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
等
因此,对于TBM,它可能有四个现有记录,1000,1001,1002,1003
要插入TBM的下一条记录,我必须知道下一个数字
值,这将是1004.如果我从VBA表格中设置了
表库存中的字段的所有值,我可以使用以下代码插入记录。 (var1对应于表单中的'Number''值,var2表示来自表单的''Plant''值。
appAccess.DoCmd.RunSQL _
"(插入库存值(''" _
& var1&"'',''" _
& var2&"'',''" _
& var3&"'',''" _
& var4&" ;'',''" _
& var5&"'',''" _
& var6&"'', ''" _
& var7&"'',''" _
& var8&"'',''" _
& var9&"'',''" _
& var10&"'',''" _
& var11&"''))"
但是,我想要做的是创建一个将计算下一个的插入
可用数量基于工厂价值,在c TBM的一个,1004。我有
尝试以下但它似乎没有工作,虽然嵌套的选择
自己工作正常。任何想法。
appAccess.DoCmd.RunSQL _
"(插入库存值(''(_
select)最大(数字)+ 1来自库存,其中Plant ='"& var2&"`)'',''" _
& var2&"'',' '" _
& var3&"'',''" _
& var4&"'',''" _
& var5&"'',''" _
& var6&"'',''" _
& var7&"'',''" _
& var8&"'',''" _
& var9&"'',''" _
& var10&"'',''" _
& var11& ;'''')"
所有代码均为excel的VBA 6.3,数据库为Access 2003 SP2。如果您需要更多代码,请告诉我。
非常感谢提前。
Martin。
Hi. My first post on the forum and I hope someone can help with this problem. I have a table called ''Inventory''. It has 11 fields, two of them being ''Num'' and ''Plant''. There are a number of ''Plants'' and the corresponding number ranges within those plants are:
Plant Number
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
etc.
So for TBM for example, it may have four existing records, 1000, 1001, 1002, 1003
To insert the next record for TBM, I would have to know the next Number
value, which would be 1004. If I set all the values for the fields in the
table Inventory from a VBA form manulally, I can use the following code to Insert the record. (var1 corresponds to the ''Number'' value from the form and var2 the ''Plant'' value from the form).
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (''" _
& var1 & "'', ''" _
& var2 & "'', ''" _
& var3 & "'', ''" _
& var4 & "'', ''" _
& var5 & "'', ''" _
& var6 & "'', ''" _
& var7 & "'', ''" _
& var8 & "'', ''" _
& var9 & "'', ''" _
& var10 & "'', ''" _
& var11 & "''))"
However, what I want to do is create an Insert that will calculate the next
available Number based on the Plant value, in the case of TBM, 1004. I have
tried the following but it doesn''t seem to work, although the nested select
works fine on its own. Any ideas.
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (''( _
select max (Number) + 1 from Inventory where Plant = `" & var2 & "`)'', ''" _
& var2 & "'', ''" _
& var3 & "'', ''" _
& var4 & "'', ''" _
& var5 & "'', ''" _
& var6 & "'', ''" _
& var7 & "'', ''" _
& var8 & "'', ''" _
& var9 & "'', ''" _
& var10 & "'', ''" _
& var11 & "''))"
All code is VBA 6.3 from excel and the database is Access 2003 SP2. If you need anymore code, let me know.
Many thanks in advance.
Martin.
为什么不试试这个:
选择MAX(NUM)其中NUM = TBM
text0.value = MAX(NUM)
text0.value = NUM + 1
Why not try this:
Select MAX(NUM) where NUM= TBM
text0.value=MAX(NUM)
text0.value=NUM+1
假设您的字段编号实际上是一个数字数据类型。
你可以在尝试插入之前将增量值分配给var1。
Assuming your field Number is actually a number data type.
You could assign the incremented value to var1 before trying to insert.
假设您的字段数字实际上是数字数据类型。
您可以在尝试插入之前将增量值分配给var1。
Assuming your field Number is actually a number data type.
You could assign the incremented value to var1 before trying to insert.
这篇关于SQL从VBA插入到Access数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!