You can't open a recordset using an action query. Instead, you can use the Execute method.
mCurrDB.Execute s0, dbFailOnError
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
Can you help to error "Invalid operation" due to last line below?
Option Explicit
Private mDbEngine As DAO.DBEngine
Private mCurrDB As DAO.Database
...
Do While True
RowID = RowID + 1
If Trim(ActiveSheet.Cells(RowID, 1).Value) = "" Then
Exit Do
End If
s0 = "insert into t1 values (" & CStr(RowID) & ",'" & Trim(ActiveSheet.Cells(RowID, 1).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 2).Value) & "','" & _
Trim(ActiveSheet.Cells(RowID, 3).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 4).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 5).Value) & _
"','" & Trim(ActiveSheet.Cells(RowID, 6).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 7).Value) & "','" & _
Trim(ActiveSheet.Cells(RowID, 8).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 9).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 10).Value) & "');"Set rs = mCurrDB.openrecordset(s0)
You can't open a recordset using an action query. Instead, you can use the Execute method.
mCurrDB.Execute s0, dbFailOnError
There is still error. Please see error like
due to last line below
Do While True RowID = RowID + 1 If Trim(ActiveSheet.Cells(RowID, 1).Value) = "" Then Exit Do End If s0 = "insert into [" & ActiveSheet.Name & "] values (" & CStr(RowID) & ",'" & Trim(ActiveSheet.Cells(RowID, 1).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 2).Value) & "','" & _ Trim(ActiveSheet.Cells(RowID, 3).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 4).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 5).Value) & _ "','" & Trim(ActiveSheet.Cells(RowID, 6).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 7).Value) & "','" & _ Trim(ActiveSheet.Cells(RowID, 8).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 9).Value) & "','" & Trim(ActiveSheet.Cells(RowID, 10).Value) & "');" mCurrDB.Execute s0, dbFailOnError