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.
- 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).The source files the master excel file gets the data are multiple excel files. It uses power query to link to those files and consolidates them. All source excel files have a sheet with the same names and structure format.
On the one hand, you want to eliminate Excel, then, but the current system depends on a number of Excel source sources. I assume those source files won't be eliminated? Just the Master file that consolidates them?
The process of searching the folder for Excel source files and then importing the data from them into Access seems to be one viable option.
Another option would be to engage is a larger project to move all of the source data collection into an Access relational database application.
To be honest, that second option seems to me to be the better choice. It eliminates the problem of multiple versions of the truth found in multiple spreadsheets, plus it consolidates the data to make reporting more straightforward. If I were consulting for your organization, that would be course I would recommend.
I retired some time ago, so I don't have the template code for the File Search operation handy. It may be in an archive file. However, it is always a possibility. The question is how much time and budget do you have available for this project. If you go the folder search and import route, there will be development costs (your time, at the very least, at whatever your hourly rate is to the organization). If you want to replace the current approach, there will be development costs, and probably greater costs. There's also the matter of your experience, time and expertise to do either. Does it fall in the scope of your role? Do you have the experience to tackle either project? Is it worthwhile to your organization to have you gain the necessary knowledge and experience. I'm always eager to recruit new Access developers; it's a great skillset to have, but it doesn't suit everyone.
At the end, though, you would either have a fragile file import process or a more robust relational database.
Think through the factors and discuss it with your colleagues and whoever controls the budget.