Mar 31 2021 09:46 AM
Mar 31 2021 09:46 AM
Is there a way to do this? Power BI and excel can both get data from a folder, not just a file. I'd like to be able to get the data from the most recent file added to a folder when the DB is refreshed. Do I need to write this in SQL?
Mar 31 2021 06:41 PM
What do you mean "...get data from a folder..."?
In Windows, data is in files, files are in folders.
If you can explain more clearly what it is that you have in that folder, we could attempt some suggestions.
That said, Access is very powerful in importing data from a large variety of data sources, so I would imagine what you want is highly doable, we just need a clearer picture of that data....
Apr 01 2021 05:03 AM
Hello @George Hepworth,
Thanks for getting back to me. What I am trying to do, is connect to the folder and use Power Query Editor in order to load into Access the most recent file regularly dropped into that folder. Both PowerBI and Excel have the functionality in the picture below.
From what I was googling, you can use SQL to do this, but I'd like to avoid that if possible. I'm trying to show my co-workers they can do this instead of copying and pasting data. Currently, I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel.
Let me know if you need additional info.
Apr 01 2021 10:18 AM
What FILES are in that folder to which you are referring. You are importing FILES, not FOLDERS. I imagine they are .csv or .xls or .xlsx FILES, are they not?
Or to put it more accurately, that dialog points you to a FOLDER containing FILES, and once you identify the FOLDER, the next dialog offers you a choice of the FILES in that folder.
And that is exactly the same thing Access does, albeit the dialog is slightly different. It looks like you are already doing it, in fact.
"I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel." To be more accurate, that data is coming from an .xls or .xlsx FILES in that folder.
Apr 01 2021 10:34 AM
Apr 01 2021 10:58 AM
Access doesn't work that way (nor does Excel really, as I previously noted). Once you browse to the folder, you still have to pick one file, .csv or .xlsx or whatever.
Also, you have revealed another critical detail not present earlier:
"The linked tables I am pulling in is data set as a table within a .xlsx file."
You MUST use the .xslx file to get to anything within that file, including that table. So, the whole request seems a bit unrealistic in that sense. In other words, that data set does not exist outside, or independently, or separately, from its container, which is the .xlsx file. Hence, the data has to be sourced via the .xlsx file.
I suppose you could save the data set in a different file format, but that presents the same circumstance. It would not be possible to have a persisted data set independently of the file containing it. Interestingly enough, disconnected data sets can be instantiated as recordsets in memory from within a relational database engine, but that's no help here.
You have to have a persisted dataset, laid out as a table, and that table must be in another container, i.e. a file, or in this case, the .xlsx file.
Is it not possible to create a permanently linked table from Access to the table in Excel? We do that frequently. The data in the table can be updated in Excel and that new data is reflected in the Access table.
Apr 01 2021 11:19 AM
Thanks for all the info. It looks like I'll have to have the Excel file make the connection to the folder, then used the linked table function in access to get the day. I'm going to perform what's described in your last paragraph.
Thanks for the info!
Apr 01 2021 11:51 AM
At the risk of flogging a dead horse, Excel is NOT connecting to the folder either. It is connecting to a file (.csv file) within that folder. The data connection dialog specifying "Folder" requires that you browse to the folder and then pick one file from within it.
What you are doing is formatting that data in a table in Excel once you select it.
And that brings up another angle, which I should have thought of earlier. Sorry.
You CAN link Access directly to any csv file as well. So because the csv files are in the "folder", you could link Access directly to them and bypass Excel that way. I didn't think of that because it sounded like you are doing some formatting and maybe consolidating with the Excel table. But if it's just the same raw data, and all you need to do is import, or link to it, that's a possibility, too. If the .csv files change, of course, you'd have to relink to the newest set as needed.
Apr 02 2021 06:09 AM
Apr 02 2021 06:26 AM
This is where we keep talking past one another, I'm afraid.
"... specifically the most recent .csv file dumped into a folder."
You are NOT getting the data from the folder itself; you are getting the data from that .csv file which is inside that folder.
The good news is that Access certainly does have that ability. It would be the same thing as linking to the Excel file. You can link Access to a very disparate variety of data sources,
You can automate the process of navigating to that specific folder, locating the .csv file with the most recent file date, and importing or linking to that file. It then becomes available to Access as a linked table. I may have gotten the wrong impression about the need to put it in an Excel table. If the raw data is the same, then Excel is not useful as an intermediary step. If the data is transformed in Excel, which is the impression I got mistakenly, then it would be needed.
I don't have sample code at hand, but I have done similar tasks many times, Access supports VBA functions, such as Dir() which can locate files in folders. Here is an older download which does what you need to do, for example.
Apr 03 2021 09:51 AM - edited Apr 03 2021 10:37 AM
I slept on this and decided a demo would be a good way to convey a better understanding of the tasks needed to accomplish what I think you want to do. It's not simple, but it is highly doable.
The attached zip file contains an accdb which has two basic functions:
It will ONLY select those three file extensions, which is what I assumed you wanted from this discussion.
When the new files are linked as tables, the tables are named for the original file name as it was found in the folder and with the date of that file formatted as "YYYYMMDDHHMMSS" so you can track of previously linked files.
PLEASE NOTE: Prior to using this you must specify the path to the folder where you want it to work, i.e. the folder on your network where the new .csv files and any .xls or .xlsx files are stored.
Otherwise it will fail.
e.g. "G:\YourReportFolder" where G is a mapped drive on your network.
If you have trouble in this part, keep adjusting the folder so that it correctly identifies the right location.
Apr 07 2021 08:33 AM
@George Hepworth Thank you for putting all of this together. I hope all of this wasn't for me and you had it already made. This id a lot of code.
I tried running it but got this error:
Do you know off-hand what I can change in order to use this? Also, do you thnk this is a code that I can integrate into my in-use Accdb? I'd like to have a few tables within one DB update when I hit refresh.
This concept is above my level of understanding. If this is too much to explain, don't worry about this. I appreciate your help thus-far.
Apr 07 2021 08:54 AM
@HarrisonBeck I did not realize you were using the 64 bit version of Office. I'll take a quick look. I'm trying to update all of my sample and demo databases for 64 bit, but that one is still in the queue. Unfortunately, I may not get to it right away today.
Apr 07 2021 09:26 AM
Hm. This works on my own 64 bit Access, so I'm not sure where the problem lies, unless I uploaded the wrong version.
Here's the relevant code that error normally would apply to. It's already been updated in my 64 bit environment, though:
#If VBA7 Then
Private Declare PtrSafe Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
That is called in
Public Sub Sleep(ByVal lngMilliSec As Long)
If lngMilliSec > 0 Then
I don't think it's used in this demo, so maybe you can comment it out both the API Declaration and the Sub that uses it, and see if that works.
No problem on the work involved. This is consolidated from two or three other working databases I've produced over the years, albeit combined here. I now use it primarily to search for image files to import into a website, but it originated with a window manufacturer who had daily csv file dumps somewhat similar to your requirement. (Hence my insistence that it would work.)
I intend to offer it as a download on my website as well, once I figure out what the issue is. As noted, I am not getting an error. I am using Access 2019 Professional 64 bit, so it should behave the same as Access 365 64 bit.