MS Access 365 - External Link to Folder

Question

Hello Community!&nbsp;I currently have an excel file (master) that uses PowerQuery that is linked to a folder in a SharePoint with several excel files (reports). Master consolidates 'pricing_report' sheets of the files in the folder. PowerQuery performs filters, cleaning, etc and returns a table consisting of all the reports in the folder. As transactions grow, the Master file is growing big in size and it is getting slow to run.&nbsp;&nbsp;I want to have the same setup in Access where I have it linked to the folder with the excel reports (new reports are dumped in the folder on a regular basis). File names do not stay consistent.&nbsp;In Access I tried using the External Data tab, Import &amp; Link but I don't see where it links to a folder.&nbsp;Please advise on how to achieve it.&nbsp;&nbsp;Thanks in advance.Alex.

george_hepworth · Answer

alex_n&nbsp;&nbsp;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?&nbsp;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.&nbsp;Another option would be to engage is a larger project to move all of the source data collection into an Access relational database application.&nbsp;To be&nbsp; 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.&nbsp;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.&nbsp;At the end, though, you would either have a fragile file import process or a more robust relational database.&nbsp;&nbsp;Think through the factors and discuss it with your colleagues and whoever controls the budget.&nbsp;&nbsp;

george_hepworth · Answer

If you would like to pursue a coding approach, let me know. I've archived almost all of the client work I did before retirement. The code I would offer as a template is on some backup on an older, unused, computer. If it would help you, it would be worth while doing a search. Or, as I noted before, you probably need to have a meeting of the minds within your organization to decide how to move forward.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.

alex_n · Answer

Hello Community, I have over 70 views on this post, but don't see any replies. Is the task achievable? Please let me know. Thanks again.

george_hepworth · Answer

alex_n&nbsp;Long story short, no. You can't link to a FOLDER as a data source in Access.&nbsp;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.&nbsp;There is a longer answer.&nbsp;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.&nbsp;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.&nbsp;&nbsp;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.&nbsp;The final consideration, therefore, is how much work you plan to invest in achieving this functionality.&nbsp;&nbsp;

alex_n · Answer

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.

Forum Discussion

MS Access 365 - External Link to Folder

Share

Resources