Move data from Excel 2007 to Access 2007 using VBA

[Mohit]

Hi

I am trying to run following code but it is giving me error. There are 9 columns that I wish to export on click of a button. Can anyone help on this?

Thanks


Error -
---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '3706':

Application-defined or object-defined error
---------------------------
OK Help
---------------------------



Code starts here -



Option Explicit


Sub ExportValsErrors()

Dim cnn As Object
Dim dbpath As String
Dim dbconnection As String
Dim strsql As String


dbpath = "C:\Users\Mohit\Downloads\New Folder\TestDB.accdb"

dbconnection = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & dbpath & ";User Id=admin;Password=;"


Set cnn = CreateObject("Adodb.connection")

Dim strQAID As Long
Dim strCompanyName As String
Dim dtTransactionDate As Date
Dim strResearcher As String
Dim strQA As String
Dim strProcess As String
Dim strQAPerson As String
Dim strQAStatus As String
Dim dtQADate As Date

Dim rngData As Range
Dim iRow As Object
Dim iCell As Object

cnn.Open dbconnection

With Sheets("sheet1").Range("A1")
Set rngData = .Offset(1).Resize(.CurrentRegion.Rows.Count - 1, 9)
End With

For Each iRow In rngData.Rows

strQAID = iRow.Cells(1, 1).Value
strCompanyName = iRow.Cells(1, 2).Value
dtTransactionDate = iRow.Cells(1, 3).Value
strQA = iRow.Cells(1, 4).Value
dtQADate = iRow.Cells(1, 5).Value
strProcess = iRow.Cells(1, 6).Value
strQAPerson = iRow.Cells(1, 7).Value
strResearcher = iRow.Cells(1, 8).Value
strQAStatus = iRow.Cells(1, 9).Value

strsql = "insert into tblQADetails " & _
"(" & _
"QAID," & _
"CompanyName," & _
"TransactionDate," & _
"QA," & _
"QADate," & _
"Process," & _
"QAPerson," & _
"Researcher," & _
"QAStatus" & _
")" & _
"values " & _
"(" & _
strQAID & ",'" & _
strCompanyName & "'," & _
dtTransactionDate & ",'" & _
strQA & "'," & _
dtQADate & ",'" & _
strProcess & "','" & _
strQAPerson & "','" & _
strResearcher & "','" & _
strQAStatus & "'" & _
")"

Debug.Print strsql
cnn.Execute strsql

Next

'Closing the connection
cnn.Close
'Deconstructing the cnn object
Set cnn = Nothing

End Sub