Forum Discussion
MS Access 365 - External Link to Folder
Long story short, no. You can't link to a FOLDER as a data source in Access.
Access works with tables, i.e. your data must be tabular, with records and fields (rows and columns in Excel worksheets). So a folder won't work.
There is a longer answer.
However, Access does support other ways to achieve a similar purpose, assuming I understand your goal correctly.
The devil is in the details. It would require custom VBA code to a large extent, so a canned solution would not be available. That's probably part of the reason for not getting responses.Finally, it's kind of a lot of work and would require details of the folder path and what your files would be and what you actually need to do with them.
I have done this kind of work in the past, but unless you are comfortable writing VBA, so doing a copy/paste of some of that custom code would be a bit much.
The final consideration, therefore, is how much work you plan to invest in achieving this functionality.
- George, thanks for the detailed response. It does seem a bit of a work in order to achieve the task. I am open for alternative approaches using Access. Having an excel file as a middle man here is no option as I am trying to avoid using excel as it is getting inflated already with more appends and it requires additional steps that my cause unnecessary errors along the way.
- It depends, probably, on the original source of the data. It's in Excel now for a reason, but does Excel get it from another source? Or is Excel, the sole source? You mention wanting to bypass Excel entirely which suggests the data is coming from other sources. Depending on what that actually looks like, you could possibly use ODBC links to other data sources, and directly use that data from Access. That would transfer the task of creating those reports to the Access interface.
Another option could be, if you can create a permanent link to the Master Excel file, and pull the data directly from it into Access, then you have a relatively straightforward path. However, if you need transformed data from the "report" Excel files, that requires code to loop the folder and bring in each existing Excel worksheet. I've done that for a couple of clients; it takes work but can be done. It sounds like the current desire is not to continue that. It's a common problem. Excel data proliferates and mutates out of control.
I guess, rather than head down one specific path, I would recommend reviewing the entire system with your users and see what they really need (not just want).
