I have an Access database which needs to take monthly updates from an excel spreadsheet. The spreadsheet has meaningful column headings which don't match the database tables. In addition, I need to check whether data is to be merged or appended. So I want to obtain a dataset from the Spreadsheet which I can loop through and have my code decide what to do with the data. I could create a temporary table in Access which exactly matches the spreadsheet structure, populate this with DoCmd.TransferSpreadsheet acImport, do my stuff and then delete it.
Is this the simplest method, or can I do something like:
strQuery = "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
& Chr(34) & fromFile & Chr(34) & "].[sheet1]"
Set rs = CurrentDb.OpenRecordset(strQuery)
This code produces the error: Cannot Update. Database or object is read only