使用变量的 Excel VBA INSERT INTO 语句

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1792399/
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-11 11:03:05  来源:igfitidea点击:

Excel VBA INSERT INTO statement using variables

excel-vbaexcelsqlms-accessvba

提问by ScottK

I am using an SQL query to insert data from an excel worksheet into an Access database. I put all the data that I want from the worksheet into variables:

我正在使用 SQL 查询将 Excel 工作表中的数据插入 Access 数据库。我将工作表中我想要的所有数据放入变量中:

rwyNumber = Range("b13").Value & Range("c13").Value
testDate = Range("b5").Value
testTime = Range("b6").Value
rwySide1 = Range("b14").Value
firstThird1 = Range("b28").Value
secondThird1 = Range("b29").Value
thirdThird1 = Range("b30").Value
averageFriction1 = Range("b23").Value

If I want to store testDate and testTime as Date/Time, do I need to convert them when I store them in the variable (above) or will access automatically make them a Date/Time as long as I have the data type set to Date/Time for those columns in the table? Also with doubles and strings.

如果我想将 testDate 和 testTime 存储为日期/时间,我是否需要在将它们存储在变量中时转换它们(上图),或者只要我将数据类型设置为日期,就会自动将它们设置为日期/时间/ 表中那些列的时间?还有双打和字符串。

What is the correct syntax for the rest of my query:

我查询的其余部分的正确语法是什么:

stSQL1 = "INSERT INTO Friction Tests  (Runway Number, Runway Test Side, Test Date, Test Time, 1/3, 2/3, 3/3, Average)"
stSQL1 = "VALUES ( " ....

The table has an auto PK. The first two values are strings, the next two are Date/Time, and the last four are doubles.

该表具有自动PK。前两个值是字符串,接下来的两个是日期/时间,最后四个是双精度值。

回答by Fionnuala

Why not make use of date/time field to include test time?

为什么不使用日期/时间字段来包含测试时间?

It is best to format dates, assuming that b5 is a date type:

最好格式化日期,假设 b5 是日期类型:

testDate = Format(Range("b5").Value,"yyyy/mm/dd")

You will end up with text, but this is not important.

您最终会得到文本,但这并不重要。

stSQL1 = "INSERT INTO [Friction Tests] ([Runway Number], [Runway Test Side], " _
& "[Test Date], [Test Time], [1/3], [2/3], [3/3], [Average]) " _
& "VALUES ( " & rwyNumber  & ",#" &  testDate   & "#,#" & testTime   _
& "#," & rwySide1   & "," & firstThird1 _
& "," & secondThird1   & "," & thirdThird1 _
& "," & averageFriction1 & ")"

If any of the fields are text, the value will have to be surrounded by single quotes or two double quotes, in a similar way to date and time.

如果任何字段是文本,则值必须用单引号或两个双引号括起来,与日期和时间类似。

I strongly recommend getting rid of spaces in field (column) and table names.

我强烈建议去掉字段(列)和表名中的空格。