Multi-Select listbox -- value selection drives import routines and append queries

[skydivetom]

Experts:

I need some assistance with reworking some data handling/management (via VBA routines in MS-Access).

Before going into the specifics for the VBA, please allow me to provide some high-level background first.

Existing process (see example file "VBA Code from Another File.txt" for details):
================================================== ===============================
1. I have a folder with 38 MS-Excel files (e.g., "AccidentalDischarge.xls", "AlcoholRelatedIncident.xls", etc.) on my computer. It is noteworthy to point out there are 13 different field/column structures for these 38 Excel files.

2. Depending on business/analysis activities, the 38 Excel files are updated at different frequencies. In other words, some files are up-to-date with current data while other Excel files may not have been updated in some time.

3. At this time, in MS-Access, I bring up a form with a listbox containing several "Steps".

4. When clicking on the listbox (e.g., "Step 1"), the following actions are perform through VBA:
4a. Drop all records from a MASTER table (i.e., "000_tblRawData").
4b. Drop all records from 38 TEMP tables (e.g., "tblRawData_AccidentalDischarge", "tblRawData_AlcoholRelatedIncident", etc.).
4c. Run 38 *import routines" which import all of the data from the 38 Excel files into the 38 TEMP tables (regardless whether or not updates to the Excel files were made).
4d. Run 38 *append queries" which add all records/data from the 38 TEMP tables (again, each of the have different field structures) into the MASTER table which accommodates the total # of different fields from the 38 Excel files.


However, I would prefer to only run the IMPORT ROUTINES and APPEND QUERIES for those Excel files which were recently updated. In essence, if this week's analysis requires me to only review 3 out 38 "categories", I don't need to use up CPU time for importing the other 35 EXCEL files (each can have thousands of records).


// BREAK BREAK //


To address a more efficient process, I have created a separate Access file "Multi Select" (see attached). This file is for testing only at this time. Here's what the file does:

1. Upon clicking on the form "frmSelectCategories", the user can select any/all of the available categories. In this example, I have only included 7 categories... but again, in actually, it will be 38 categories.


2. Once a category selection has been made, I click on "Run Query" which updates the append query and then appends all matching records from "000_tblRawData" into "001_tblRawData_SelectedCategories"


// NOW, HERE'S WHAT I NEED SOME HELP WITH... //

- I want to apply the concept of the Multi Select database (i.e., choosing a subset of categories from the listbox) and modify the existing process (see VBA in attached text file).
- So, again, if this week's analysis requires only the assessment of a few categories only, then the selection of those categories will execute, e.g., only 3 import routines and 3 append queries. Therefore, I reduce CPU time by only
importing the Excel files needed at that time (vs. all Excel files).

I hope the above was not too confusing... any assistance with modifying the **VBA code in the TEXT file** would be greatly appreciate.

Thank you,
EEH

[ranman256]

Instead of importing 38 tables, cant you just do a 1 time Link (well 38) and link these files as external tables?
the data is always live, thus eliminating the time to import.

[skydivetom]

ranman256:

Thank you for the response and feedback... I appreciate it.

Ok, I'm open to further explore this... I have not "played" with linking Excel files to Access tables. While I will look into it, could you please further explain how this works?

If it turns out to be a viable scenario, would the "replacement" of rows in the Excel files cause any issues w/ the linking. In other words, while the Excel file names would never change, the number of rows may change from, e.g., 1000 to 1100 in one week... and back to 900 in the following week.

Again, I'd appreciate if you could further elaborate on the "linking" scenario between Excel and Access.

Thank you,
EEH

[skydivetom]

ranman256:

I just checked it out... that concept works perfectly!!!!

I simply was never aware of it. I will update my VBA accordingly and remove the Import routines. THOUSAND THANKS!!!!

[skydivetom]

ranman256:

I just checked it out... that concept works perfectly!!!!

I simply was never aware of it. I will update my VBA accordingly and remove the Import routines.

Quick follow-up:
- So, I have tested the linking of two (2) external Excel files to the associated 2 tables in Access. This works great!
- Now, given the 13 different files structures, I still need the MASTER table to hold the total of all records from the TEMP tables.

My question: Is there a way I can also link the 38 TEMP tables to the MASTER file? This would eliminate the need to run the 38 append queries. If so, how can that be done?

Thanks!

[Gicu]

Look into using a UNION query (note that it is not updatable).