Forum Discussion
Get DB Data From Folder
"... add "From Folder" to the dropdown ..."
I don't think that's in the cards for Access, for a number of technical and practical reasons. As I previously noted, VBA is a very powerful programming environment. You could create a procedure to append the records from one or more identically structured csv or xlsx files if you can be certain that they are actually consistent. I have had to do that for clients in the past, as a matter of fact. The key was a guarantee by the client that each csv would have the same columns, with the same names, in the same order, and with every record having the same datatypes in those fields. NO variations permitted.
Access requires structured data for tables: the same fields in the same order with the same datatypes in each field (no mix of text and dates, for example). You simply can't guarantee that consistency in some sort of bulk import of all items in a folder. Not unless you control the files going into the folder in the first place.
However, adding records from files with different columns or columns in different orders, which is 100% possible in Excel, without the intervention of some logic that ensures compatibility, is a bridge too far for a generic process that just asks for one or more files.
It's a nice idea, but not worth investing development time and resources in pursuing.
Replying as I have a similar but different use case and just read through the thread. I think my solution might be useful to you.
My problem: I want an Access database to link to all Excel files in a specified folder. These files contain the same kind of data, and so ideally I would rather they all aggregate into one table as opposed to each being their own.
My solution: I created a new Excel sheet and used PowerQuery (within Excel - Go to Data >> Get Data >> From File >> From Folder) to link to all Excel sheets in my specified folder. In your case, you could then specify within PowerQuery that you want the most recently-updated file or whatever else you need. Save this intermediate Excel sheet somewhere, and then link to it from a database as usual.
One caveat about this solution - I am not sure how data refresh will work here. In an ideal world, each time the database checks the linked file, the linked file should check its linked folder. That might be the case here, but I'm not sure.
Either of you should feel free to let me know if I'm way off here - I am learning and would appreciate being steered straight if I'm off course. Have a great day.
George_Hepworth Will do. And thank you.
Once again, it is not likely Microsoft will implement this.
In the meantime, look into creating a recursive VBA function to do it. Here's one such procedure:
https://stackoverflow.com/questions/30356373/using-ms-access-to-import-multiple-text-files
George_Hepworth No files are perfect but our files are identically formatted. Each 2+gb monthly folder has 67 county text files and there are a decade worth of monthly folders-all with the same headings, data type, etc. . In a perfect world, I could import a folder of folders of text files all in one fell swoop.
- On further consideration, I guess it could be possible to import multiple csv or single sheet workbooks into multiple tables in Access. And that would leave you only the task of validating the data in those tables and appending them to your master table. I'm not very optimistic it's going to happen, though.
