Mohit
07-29-2012, 09:54 AM
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
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