Export range in Excel to MS Access uisng DAO
Posted on 2015-02-08
I need to export a named range in Excel to MS Access.
Previously I was using VBA in Access to achieve this using DoCmd.TransferSpreadsheet which worked, but I would now like to avoid launching Access. I am keen on using ADO or DAO instead, which I have not used before, but from what I have read DAO may be preferable. I am using MS Office 2010.
I have started to use DAO and I have successfully exported data to Access, either adding of editing records in a table. I am however unsure how I should export a named range in Excel to Access. The data to be exported is to be appended to an existing table. The first row in each column of the named range has a header which corresponds with a field name in the Access table. The ordering of the headers in Excel matches the ordering of the field names in Access.
I would really appreciate if someone could illustrate how I could do this.
Many thanks in advance