Copy DAO recordset from Excel into Access table

Question

I have searched endlessly for a solution to this but can't figure it out at all. Any help would be massively appreciated.

I currently have some Access VBA code which sets up a DAO recordset connection with a range in an excel worksheet. (I have avoided linking the spreadsheet as a linked table as I am unsure if you can do this with just a specific range and unfortunately, editing the spreadsheets in any way is not an option).

I then need to copy this data into a table. I am currently looping through each record and adding it to a new recordset as shown below:

Set rsTbl = CurrentDb.OpenRecordset("tblData")
dbpath = "S:\OPS\Agent Performance.xls"
Set db = OpenDatabase(dbpath, False, True, "Excel 8.0;HDR=Yes;")
Set rst = db.OpenRecordset("SELECT * FROM [AgentActivity$C7:AP1000]")

Do Until rst.EOF
    rsTbl.AddNew
    For Each fld In rst.Fields
        rsTbl(fld.Name) = fld.Value
    Next fld
    rsTbl.Update
rst.MoveNext
Loop

This works well enough but I need to loop through a lot of records in each workbook and also loop through a lot of workbooks. I was hoping there was a way of dumping the entire recordset into tblData instead of having to loop through as I am currently.

Does anyone know a better way?

Leroy · Accepted Answer · 2016-11-08 15:28:17Z

2

Well after all this time of searching for a solution, I finally reach the point where I ask for help and then I figure it out anyway.

I've used the transferSpreadsheet method to just link the range I need and then an INSERT INTO statement to move the data to tblData

DoCmd.DeleteObject actable, "tblTemp"
path = "S:\OPS\Agent Performance.xls"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblTemp", path, True, "AgentActivity!C7:AP1000"
CurrentDb.Execute "INSERT INTO tblData SELECT * FROM tblTemp"

I can then loop to the next workbook and overwrite the tblTemp. It's running much more quickly now. There may be another way which is better still but at least I've made progress. Hope this is useful for someone else.

answered Nov 8, 2016 at 15:28

Leroy

6441 gold badge10 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy DAO recordset from Excel into Access table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related