Access Excel SQL Insert Into Query

0
votes

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:

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?

Here is the complete VBA code:

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
sqlexcelvbams-access
I'm not sure, but it looks like you're looking for an update statement. Alternatively maybe you just want to delete all records before re-importing them from Excel. - GolezTrol
yes, I think you are right. I didn't tought about that. my solution now: CurrentDb.Execute "UPDATE [tbl_01] SET Import01 = '" & strImport01 & "' , Import02 = '" & strImport02 & "', Import03 = '" & strImport03 & "' WHERE ID =" & [Forms]![query1]![ID] - user8643181
and thank you GolezTrol .. you saved me some time :) - user8643181

3 Answers

0
votes

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.

0
votes

Try this code instead yours

there is no need for Select after where clause

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

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

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]