Export from Excel to Access 2007 using Excel VBA

Question

I need to export the contents of a worksheet to append to an access database table (both 2007)and I am attempting to run this from a module in the Excel spreadsheet. The table has a primary key which is an autonumber and I have tried running the module below with and without an empty first column in the spreadsheet to match the spreadsheet columns with the table columns. Also the final field in the table is a checkbox Y\N and I have made the final column in the spreadsheet TRUE and FALSE values. But when I run the module I get the "Finished" msgbox but the table is not updated. Both the spreadsheet and the database are in the location of C:. What am I doing wrong?

Private Sub AddData()


Dim strMyPath As String, strDBName As String, strDB As String, strSQL   
 As  String
Dim i As Long, n As Long, lastRow As Long, lFieldCount As Long
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

strDBName = "CMDB.mdb"
strMyPath = ThisWorkbook.Path
strDB = strMyPath & "\" & strDBName

"Microsoft.ACE.OLEDB.12.0". The ACE Provider can be used for both the    
  Access .mdb & .accdb files.
  connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0;    
  data source=" & strDB


Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(18)

Set adoRecSet = New ADODB.Recordset

strTable = "Asset_Table"
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB,    
CursorType:=adOpenStatic, LockType:=adLockOptimistic


lFieldCount = adoRecSet.Fields.Count
'determine last data row in the worksheet:
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row


For i = 2 To lastRow

   adoRecSet.AddNew

   For n = 0 To lFieldCount - 1
    adoRecSet.Fields(n).Value = ws.Cells(i, n + 1)
   Next n

   adoRecSet.Update

Next i



adoRecSet.Close
connDB.Close
Set adoRecSet = Nothing
Set connDB = Nothing

MsgBox "Finished"

End Sub

EDIT: The advice of Evans and ChipsLetten below assisted and I have this resolved. I changed the row count calculation and added in an If to deal with the auto number as Chips suggested to be the following.

Dim b As Long  
b = ws.UsedRange.Rows.Count

For i = 2 To b - 1
 adoRecSet.AddNew
  For n = 0 To lFieldCount - 1
    If Not adoRecSet.Fields(n).Properties("ISAUTOINCREMENT") Then    
     adoRecSet.Fields(n).Value = ws.Cells(i, n + 1).Value
   End If

 Next n
adoRecSet.Update

Next i

Have you tried stepping through your code with the debugger to see if the loop is entered and iterating as you expected? Rather than working with a recordset you could try executing SQL quiries to append the data. — Evan
@Evan Thanks for the response. Sorry for the late reply but it was the weekend. I will try what you suggest — AJF
@Evan Thanks. I have this resolved. Your advice worked as I found it was a silly error and my code to calculate the last row was returning a value of 1 and so the outer for loop was not even running once. — AJF

ChipsLetten ChipsLetten · Accepted Answer · 2015-07-24T19:32:02

You can test the field to see if it is an auto increment field before trying to write a value to it. Try the below code which works ok for me (Excel 2007 but Access 2010)

For i = 2 To lastRow

    adoRecSet.AddNew

    For n = 0 To lFieldCount - 1
        If Not adoRecSet.Fields(n).Properties("ISAUTOINCREMENT") Then
            adoRecSet.Fields(n).Value = ws.Cells(i, n + 1).Value
        End If
    Next n

    adoRecSet.Update

Next i

Using TRUE/FALSE for the final column of the Excel sheet works fine for me.

Export from Excel to Access 2007 using Excel VBA

1 Answers