Forum Discussion
MS Access 365 - External Link to Folder
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.
I agree that Excel is not a good solution as you've outlined it, but an appropriate alternative probably needs to come from a conscious selection. The costs alone, let alone the applicability of the solution would indicate that.