Experts:
In an existing routine, I import XLSX files via a simple VBA routine. That is, I merely browse to the folder containing the desired spreadsheets and click 'Ok'. While this process works great, I would like to now adjust so that I can import ACCDB tables in a similar fashion.
That is, preferably, I do NOT want to use a "stored process" (under the *External Data* ribbon) since I will store the local folder path (of my computer). Furthermore, the "Get External Data" allows me to import from only one (1) DB file at the time (vs. a batch routine of every ACCDB in the source folder).
Instead, just like now with the Excel routine, I would like to bring up the "Browse Dialogue" and then point to the folder.
The attached zip file contains the following:
1. Folder "Import_Files" includes 2 XLSX and 2 ACCDB files.
2. Also, attached DB "Import_Routine" includes a single form object "F01_MainMenu".
Current Excel Process:
1. I open the form and click on "Import Data"
2. I then browse to folder "Import_Files" and click "OK".
3. Now, both spreadsheets are imported and their table names are derived from their tab names + prefix "01_tbl_". For example, XLS file #1 with tab name = "Source_1" is imported as "01_tbl_Source_1".
New Access Process:
- Just like with the Excel routine, I want to click on the form's command button "Import Access Tables".
- ... then browse to the same folder...
- and then, I want the tables be imported as "01_tbl_Source_3" and "01_tbl_Source_4" for DB files File_3 and File_4, respectively.
... that's pretty much it! Again, my preference is NOT to use a stored/saved procedure from the "External Data" ribbon.
That said, what VBA would allow me to mimic the existing VBA routine but import the ACCDB tables vs. XLSX tabs? That is, the VBA routine would have to scan through any ACCDB file in the source folder an import **all tables** from **all files** via a single click.
Thank you for any assistance in advance,
Tom