访问Excel SQL插入查询

I try to import data from specific MS-Excel data cells (A2, B2, C2) into a MS-Access Table. It does work with the following Code:

我尝试将特定MS-Excel数据单元（A2，B2，C2）中的数据导入MS-Access表。它适用于以下代码：

CurrentDb.Execute "INSERT INTO [tbl_01] ( Import01, Import02, Import03 ) VALUES('" & strImport01 & "', '" & strImport02 & "', '" & strImport03 & "')"

Only problem is, that it inserts the data into an new dataset on that Access Table. But I want to achieve that the data crom Excel should be inserted into the selected dataset (ID) on the Access-form "query1" .. I am not sure why it won't work. Do you have an idea?

唯一的问题是，它将数据插入到该访问表上的新数据集中。但我希望实现Excel中的数据应该插入到Access-form“query1”上的选定数据集（ID）中。我不确定为什么它不起作用。你有好主意吗？

Here is the complete VBA code:

这是完整的VBA代码：

Set xlapp = CreateObject("Excel.Application")
Dim strImport01 As String
Dim strImport02 As String
Dim strImport03 As String
Dim fileName As String
Set xlwb = xlapp.Workbooks.Open(fileName)
Set xlws = xlwb.Worksheets(1)
fileName = CurrentProject.Path & "\test.xlsx"

strImport01 = xlwb.Sheets(1).Range("A2")
strImport02 = xlwb.Sheets(1).Range("B2")
strImport03 = xlwb.Sheets(1).Range("C2")

CurrentDb.Execute "INSERT INTO [tbl_01] ( Import01, Import02, Import03 ) VALUES('" & strImport01 & "', '" & strImport02 & "', '" & strImport03 & "') SELECT ID WHERE ID =" & [Forms]![query1]![ID]"

3 个解决方案

#1

I think you need to move

我想你需要搬家

fileName = CurrentProject.Path & "\test.xlsx"

to the top.

到顶部。

Dim strImport01 As String
Dim strImport02 As String
Dim strImport03 As String
Dim fileName As String

fileName = CurrentProject.Path & "\test.xlsx"
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Open(fileName)
Set xlws = xlwb.Worksheets(1)

Hope it help.

希望它有所帮助。

#2

Try this code instead yours

请尝试使用此代码

there is no need for Select after where clause

不需要Select after where子句

" INSERT INTO [tbl_01] " _ 
        & "( Import01, Import02, Import03 ) VALUES(" _ 
    & strImport01 & "', '" & strImport02 & "', '" & strImport03 & ")" _
        & "WHERE ID =" & [Forms]![query1]![ID];"

#3

for anybody who has the same or any similar problem .. my solution with the UPDATE Statement:

对于任何有相同或类似问题的人..我的UPDATE声明解决方案：

fileName = CurrentProject.Path & "\test.xlsx" 
Set xlapp = CreateObject("Excel.Application")

Dim strImport01 As String
Dim strImport02 As String
Dim strImport03 As String

Set xlwb = xlapp.Workbooks.Open(fileName)
Set xlws = xlwb.Worksheets(1)

strImport01 = xlwb.Sheets(1).Range("A2")
strImport02 = xlwb.Sheets(1).Range("B2")
strImport03 = xlwb.Sheets(1).Range("C2")

CurrentDb.Execute "UPDATE [tbl_01] " & _
" SET Import01 = '" & strImport01 & "' , Import02 = '" & strImport02 & "', Import03 = '" & strImport03 & "' " & _
" WHERE ID =" & [Forms]![query1]![ID]

#1