Forum Discussion
Get DB Data From Folder
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.
- gdrahalNov 16, 2023Copper ContributorIt would be unweidly for MS Access developers but they could add "From Folder" to the dropdown and let it append data the same way so we end up with a 150 MM row table without a thousand import operations. Excel has a row limit and Access apparently doesn't.
- George_HepworthNov 16, 2023Silver Contributor
"... 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.
- George_HepworthNov 16, 2023Silver ContributorOn 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.