Forum Discussion
Get DB Data From Folder
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:
- Loop through all of the files in a designated folder (and in any subfolders under that designated folder) and list them in a table in the database by file name, path, date created and last modified date. The list is displayed in a form which you can then use to:
- Select any one of the files to link as text files (if they are .csv format files) or as Excel files (if they are .xlsx or .xls format files).
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.
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.
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.
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)
#Else
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
#End IfThat is called in
Public Sub Sleep(ByVal lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End SubI 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.
I have the same problem and the replies have been incorrect. The above screenshot showing "Get Data, From File, From Folder" permits the bulk import of multiple files (all formatted the same) into Excel. In Access "External Data, New Source Data, From File..." does not have the "From Folder" option found in Excel. Is there a way to bulk import the contents of a folder into Access? Or, get the "From Folder" option?- No, there is not and you'd find it unwieldy if there were.
Basic rule: Excel is not like Access. When using Excel, one has certain tools and functions available. When using Access, one has other tools and functions available.
While it would theoretically be possible to implement bulk imports from multiple worksheets into Access, the reality of how Access tables work would make that difficult.
You always have the option of automating Excel, though, using VBA to import multiple external data sources, such as a CSV or worksheet.