Insert all rows to access database

[krasi_e_d]

Hello, people

I use this vba code to insert data in access database 2007. It's working for one row, but when I try to use for more rows and columns give me "Type mismatch".

Sub Simple_SQL_Insert_Data()

Dim cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Dim oWS As Worksheet

Set oWS = ActiveSheet

'Dim sName As String 
Dim sLocation As String
Dim iRecAffected As Integer

On Error GoTo ADO_ERROR

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ked\Desktop\last-padej\Database6.accdb;Persist Security Info=False"
cn.ConnectionTimeout = 40
cn.Open

sName = oWS.Range("e14").Value
sLocation = oWS.Range("f14").Value
'sName = "Krishna Vepakomma"
'sLocation = "Narayanaguda"

Set oCm = New ADODB.Command
oCm.ActiveConnection = cn
oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"
oCm.Execute iRecAffected

If iRecAffected = 0 Then
   MsgBox "No records inserted"
End If

If cn.State <> adStateClosed Then
   cn.Close
End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing
If Not cn Is Nothing Then Set cn = Nothing

ADO_ERROR:
   If Err <> 0 Then
      'Debug.Assert Err = 0
      MsgBox Err.Description
      Err.Clear
      Resume Next
   End If

End Sub

[John_G]

Your code as shown above inserts only one record to MS Access - what does the code that does not work look like, and where does it fail? To find out, turn off the error handling (comment out On Error...), and use debug mode to show you (and us) where the error is.

John

[June7]

Presume code is behind Excel.

I don't see code that involves more than one row.

Show the code you attempted for multiple rows.

iRecAffected is declared and used but how would it ever not equal 0? I don't see it being incremented.

I don't understand this line: oCm.Execute iRecAffected

Could you just create Access link to the Excel sheet?

[krasi_e_d]

Your code as shown above inserts only one record to MS Access - what does the code that does not work look like, and where does it fail? To find out, turn off the error handling (comment out On Error...), and use debug mode to show you (and us) where the error is.

John

Presume code is behind Excel.

I don't see code that involves more than one row.

Show the code you attempted for multiple rows.

iRecAffected is declared and used but how would it ever not equal 0? I don't see it being incremented.

I don't understand this line: oCm.Execute iRecAffected

Could you just create Access link to the Excel sheet?

Guys,
I gave you different code this is the code, which I try:

Option Compare Database
Private Sub importExcelData()
Dim xlApp As Excel.Application

Dim xlBk As Excel.Workbook

Dim xlSht As Excel.Worksheet
Dim dbRst As Recordset

Dim dbs As Database

Dim SQLStr As String

Set dbs = CurrentDb

Set xlApp = Excel.Application

Set xlBk = xlApp.Workbooks.Open("C:\Users\ked\Desktop\last-padej\clear-file.xls")

Set xlSht = xlBk.Sheets(1)

'SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT, columnTre TEXT)"
'DoCmd.SetWarnings False

'DoCmd.RunSQL (SQLStr)

Set dbRst = dbs.OpenRecordset("excelData")

dbRst.AddNew

xlSht.Range("a13").Select

dbRst.Fields(0).Value = xlSht.Range("a13").Value

xlSht.Range("B2").Select

dbRst.Fields(1).Value = xlSht.Range("B2").Value
dbRst.Update
dbRst.Close
dbs.Close

xlBk.Close
End Sub

[June7]

Did you debug (see link at bottom of my post for guidelines)? What line is triggering the error? Still don't see code for mulitiple rows.

Want to provide the Excel file for analysis?

[John_G]

Hi -

If this is the exact code you are using, it won't work because you have not created the table ExcelData - the commands to do that are commented out. As a result, the OpenRecordset command fails. Does the error message you get indicate that is the line causing the problem?

John