Updating .accdb using Excel data from ACCESS VBA

Question

I've been looking for a method of updating an Access database with data stored in an Excel file, but I can only find methods from Excel VBA and not through Access itself which is not what I want. This would be to dynamically update an Access table with data that is regularly updated in an Excel file. I have the table in Access set up with an autonumber as a primary key. I just need a code to do this on command (like a 'refresh button').

I've found this link helpful in doing it through Excel (Updating Access Database from Excel Worksheet Data). I would like a way to do it solely in Access (without opening Excel; just referencing the data in the worksheet(s)). Is this possible?

In Access, can you create a link to the Excel worksheet, and can Access then present the data correctly when you open the link in Datasheet View? — HansUp
Doing a data import from Excel is how I set up my table initially, however, the data in Access does not seem to update when the Excel file is updated. Instead, I'm aiming to update the Access table through a SQL statement. — nsjkd
As far as a Linked Table goes, it seems to suffer from a couple of flaws. 1) You need to define the range in Excel -- this forces you to either choose an arbitrarily long range or regularly change the range name in Excel to encompass the data you want. 2) You cannot use validation rules or otherwise alter the recordset without interfacing with Excel. 3) You cannot have the Excel file open in order to have this update. You might make 1000 changes in the Excel file, but none will reflect until the file is closed, at which point Access locks the Excel file so no further changes can be made. — nsjkd
So do you want to run an Access procedure to pull in changed data from an Excel sheet before the sheet changes have been saved back to the workbook file? — HansUp
No, I think you're missing my point for #3. I mean even if the workbook is saved, Access will not update until the workbook is closed. — nsjkd

John Bingham John Bingham · Accepted Answer · 2013-09-16T23:51:46

You can't do it via an sql statement that I know of, but you can could create a button which when clicked opens the workbook/sheet and then retrieves data from it into your database - something like:

dim XL as object
dim WB as object
dim WS as object

Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
Set WB = XL.workbooks.open(pathname)
Set WS = WB.Worksheets("Sheet1")

Then retrieve data into access using the cell references of that data

Updating .accdb using Excel data from ACCESS VBA

1 Answers