SQL 如何在 MS Access 中使用 VBA 将值插入到数据库表中

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/25044447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 02:20:17  来源:igfitidea点击:

How to insert values into the database table using VBA in MS access

sqlvbaaccess-vbams-access-2010

提问by shravya

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error:

我最近开始使用访问权限。我想在数据库中插入几行;但是,我被卡住了,因为它抛出了一个错误:

Too few parameters.

参数太少。

I have a table test with only one column in it named start_date I want to insert all the dates between two dates for example if I consider 1/7/2014 to 3/7/2014 I need dates as 1/7/2014,2/7/2014,3/7/2014 in my table, but I have problem inserting the code I used is as follows

我有一个表测试,其中只有一列名为 start_date 我想插入两个日期之间的所有日期,例如,如果我考虑 1/7/2014 到 3/7/2014 我需要日期为 1/7/2014,2 /7/2014,3/7/2014 在我的表中,但我在插入我使用的代码时遇到问题如下

Private Sub createRec_Click()
Dim StrSQL As String
Dim InDate As Date
Dim DatDiff As Integer
Dim db As database
InDate=Me.FromDateTxt
'here I have used a code to find out the difference between two dates that i've not written
For i = 1 To DatDiff
StrSQL = "INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );"
StrSQL = StrSQL & "SELECT 'Test'"
db.Execute StrSQL
db.close
i=i+1
next i
End Sub

My code throws an error in the line Db.Execuite StrSQL as too few parameters. Hope somebody could help me with this issue. Thanks In advance

我的代码在 Db.Execuite StrSQL 行中引发错误,因为参数太少。希望有人可以帮助我解决这个问题。提前致谢

回答by user3267567

since you mentioned you are quite new to access, i had to invite you to first remove the errors in the code (the incomplete for loop and the SQL statement). Otherwise, you surely need the for loop to insert dates in a certain range.

由于您提到您是新手,因此我不得不邀请您首先删除代码中的错误(不完整的 for 循环和 SQL 语句)。否则,您肯定需要 for 循环在某个范围内插入日期。

Now, please use the code below to insert the date values into your table. I have tested the code and it works. You can try it too. After that, add your for loop to suit your scenario

现在,请使用下面的代码将日期值插入到您的表格中。我已经测试了代码并且它有效。你也可以试试。之后,添加您的 for 循环以适合您的场景

Dim StrSQL As String
Dim InDate As Date
Dim DatDiff As Integer

InDate = Me.FromDateTxt

StrSQL = "INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True

回答by Lord Peter

You can't run two SQL statements into one like you are doing.

您不能像现在这样将两个 SQL 语句合二为一。

You can't "execute" a select query.

您不能“执行”选择查询。

db is an object and you haven't set it to anything: (e.g. set db = currentdb)

db 是一个对象,您尚未将其设置为任何内容:(例如 set db = currentdb)

In VBA integer types can hold up to max of 32767 - I would be tempted to use Long.

在 VBA 中整数类型最多可以容纳 32767 - 我很想使用 Long。

You might want to be a bit more specific about the date you are inserting:

您可能想要更具体地了解要插入的日期:

INSERT INTO Test (Start_Date) VALUES ('#" & format(InDate, "mm/dd/yyyy") & "#' );"

回答by user3267567

  1. Remove this line of code: For i = 1 To DatDiff. A For loop must have the word NEXT
  2. Also, remove this line of code: StrSQL = StrSQL & "SELECT 'Test'" because its making Access look at your final SQL statement like this; INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );SELECT 'Test' Notice the semicolon in the middle of the SQL statement (should always be at the end. its by the way not required. you can also omit it). also, there is no space between the semicolon and the key word SELECT
  1. 删除这行代码:For i = 1 To DatDiff。For 循环必须有 NEXT 这个词
  2. 另外,删除这行代码: StrSQL = StrSQL & "SELECT 'Test'" 因为它让 Access 像这样查看您的最终 SQL 语句;INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );SELECT 'Test' 注意 SQL 语句中间的分号(应该总是在最后。顺便说一下不是必需的。你也可以省略它)。此外,分号和关键字 SELECT 之间没有空格

in summary: remove those two lines of code above and your insert statement will work fine. You can the modify the code it later to suit your specific needs. And by the way, some times, you have to enclose dates in pounds signs like #

总结:删除上面的这两行代码,您的插入语句将正常工作。您可以稍后修改代码以满足您的特定需求。顺便说一句,有时,您必须将日期括在磅符号中,例如 #