使用 excel vba 将用户表单数据插入到 Access 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11688375/
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
Insert Userform data into Access table using excel vba
提问by Reznor
I'm currently making a simple inventory system application using Excel VBA. I have a userform to get the input and I would like to save the import data into a few Microsoft Access tables.
我目前正在使用 Excel VBA 制作一个简单的库存系统应用程序。我有一个用户表单来获取输入，我想将导入数据保存到几个 Microsoft Access 表中。
I am trying to pull the data from the userform and enter it into the access table when the user hits enter. When I run this code, a new record is made with the table ID but the two records I am trying to import are left blank.
我正在尝试从用户表单中提取数据，并在用户按 Enter 键时将其输入到访问表中。当我运行此代码时，会使用表 ID 创建一条新记录，但我尝试导入的两条记录留空。
Public Sub AddDatabaseEntry()
'Initialize all variables
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stDB As String, stSQL As String, stProvider As String
Dim orderNum As String
Dim orderDate As String
orderNum = txtOrderNum
orderDate = txtDate
stDB = "Data Source= " & ThisWorkbook.Path & "\obsDatabase.accdb"
stProvider = "Microsoft.ACE.OLEDB.12.0"
'Opening connection to database
With cn
.ConnectionString = stDB
.Provider = stProvider
.Open
End With
'SQL Statement of what I want from the database
stSQL = "INSERT INTO Orders (OrderNumber, OrderDate) " & _
"Values ('" & orderNum & "', '" & orderDate & "')"
Set rs = cn.Execute(stSQL)
'Looping through the records I pulled and inserting the data into the comboBox
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Private Sub btnAdd_Click()
AddProduct
AddDatabaseEntry
End Sub
回答by Fionnuala
I suspect you have the data types wrong:
我怀疑您的数据类型有误：
tSQL = "INSERT INTO Orders (OrderNumber, OrderDate) " & _
"Values (" & orderNum & ", #" & orderDate & "#)"
I think your order number is likely to be numeric, so no quotes, and the delimiter for dates is hash (#), not a quote (').
我认为您的订单号可能是数字，所以没有引号，日期的分隔符是哈希 (#)，而不是引号 (')。
It is nearly always safer to format dates to an unambiguous format:
将日期格式化为明确的格式几乎总是更安全：
"Values (" & orderNum & ", #" & Format(orderDate,"yyyy/mm/dd") & "#)"
You cannot set a recordset to an action query, so:
您不能将记录集设置为操作查询，因此：
cn.Execute stSQL
Finally, you can save problems with data types, string problems, etc. with parameters.
最后可以用参数保存数据类型问题、字符串问题等。
stSQL = "INSERT INTO table1 (id, adate) " & _
"Values (?, ?)"
cmd.ActiveConnection = cn
cmd.CommandText = stSQL
cmd.CommandType = adCmdText
cmd.Parameters.Append _
cmd.CreateParameter("p1", adInteger, adParamInput, , OrderNum)
cmd.Parameters.Append _
cmd.CreateParameter("p2", adDate, adParamInput, , OrderDate)
cmd.Execute
As an aside, you can insert a range of numbers or a sheet into an Access table with a single query:
顺便说一句，您可以使用单个查询将一系列数字或工作表插入到 Access 表中：
INSERT INTO Table1 ( ADate )
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]
