I'm wanting to export an Excel worksheet to an existing Access table. I want to do this from within Excel using VBA and a user macro. I want to be able to append the existing records within the Access table. What's the best way to do this? Thanks.

Try this code



Sub DAOFromExcelToAccess()

' exports data from the active worksheet to a table in an Access database

' this procedure must be edited before use

Dim db As Database, rs As Recordset, r As Long

Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")

' open the database

Set rs = db.OpenRecordset("TableName", dbOpenTable)

' get all records in a table

r = 3 ' the start row in the worksheet

Do While Len(Range("A" & r).Formula) > 0

' repeat until first empty cell in column A

With rs

.AddNew ' create a new record

' add values to each field in the record

.Fields("FieldName1") = Range("A" & r).Value

.Fields("FieldName2") = Range("B" & r).Value

.Fields("FieldNameN") = Range("C" & r).Value

' add more fields if necessary...

.Update ' stores the new record

End With

r = r + 1 ' next row

Loop

rs.Close

Set rs = Nothing

db.Close

Set db = Nothing

End Sub


.NET Application Development | C# Development

Thanks for the response. Seems we have a problem at a certain area while compiling. It does not like the Dim statement as far as Dim db As Database, rs As Recordset. Any ideas?