MS Access 365 - External Link to Folder

[alex_n]

Hello Community!

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. 

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.

In Access I tried using the External Data tab, Import & Link but I don't see where it links to a folder.

Please advise on how to achieve it. 

Thanks in advance.

Alex.

[#2]

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.

[#3]

@alex_n 

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.

[#4]

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.

[#5]

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).

[#6]

@George_Hepworth 

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. 

[#7]

@alex_n 

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.

[#8]

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.