Here is a quick and simple solution that will allow you to dynamically link to Excel from MS Access. This routine creates a linked table to MS Access from Excel, that will allow you to pull in the data from Excel and reference it as a table in Access. This can come in handy for many reasons, perhaps not everyone has Access installed on their machines and you need them to update excel so you can use in some process you designed. Another example is when someone else is generating a dump from an application somewhere, and you are limited to the Excel as a dataset because of this, that, and the other reason.

As usual, here is a download of a working example of this solution. Just note, in order for it to work you need to update the iPath string to the file path you save the example files at.

[download id=”4″ format=”1″]

Link Excel to MS Access

So now that we got the working demo out the way, if you want to just snag the VBA code and get it working yourself, then here you go. This snippet will go through and establish the link to Excel in .xls and .xlsx file formats. In addition, you can modify this in several fashions to make it even more dynamic to look for things like named ranges, file naming conventions, path options, etc. Enjoy!