Excel VBA INSERT INTO语句中使用变量 [英] Excel VBA INSERT INTO statement using variables

问题描述

我正在使用SQL查询将excel工作表中的数据插入Access数据库。我把工作表中所需的所有数据都变成变量：

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:

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

如果我要将testDate和testTime存储为Date / Time，当我将它们存储在变量（上面）中时，需要转换它们，或者只要我将数据类型设置为表/列中的列的日期/时间，将自动使它们成为日期/时间。另外还有双打和字符串。

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.

我的其他查询的正确语法是什么：

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 ( " ....

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

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

推荐答案

为什么不使用日期/时间字段包括测试时间？

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

最好格式化日期，假设b5是日期类型：

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

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.

这篇关于Excel VBA INSERT INTO语句中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！