Auto-generate queries via VBA (need extended capability)

[skydivetom]

Vlad -- I love it!!



This version allows me to "overcome" all issues based on *scenario #2* (dups within same product but different organization). Excellent!!!

I still have to resolve dups based on *scenario #1* (dups within same product AND same organization). I should be able to resolve that on Tuesday or Wednesday though (as you know, Monday's a holiday).

If ok w/ you, I'll leave the post open a bit longer in the event I run into something unexpected.

Have a wonderful Sunday! Thank you for helping me out to get to this product! This version is HUGE!!!

Cheers,
Tom

[skydivetom]

Vlad:

I'm still working on the mapping... quick follow-up question (I hope you won't mind).

- Depending on the selected source (and its associated fields), a different number of products tables are generated during each execution.
- For instance, when selecting **actual** source "Alpha", I generate 16 queries (8 SEL and 8 MK queries) + 8 "tbl_" tables.
- Then, when selecting **actual* source "Echo", the previously (from "Alpha") 16 SEL/MK queries are deleted. At the same time, "Echo" results in 12 queries (6 SEL and 6 MK queries) + 6 "tbl_" tables.

While not an "issue" per se, the following is happening:
- While a new execution, deletes all SEL/MK queries, not all previous "tbl_" tables are deleted.
- So, in this example, "Alpha" resulted in the creation of 8 tables.
- Then, when running "Echo" 6 tables are created.
- However, there are 2 tables which still retain the "Alpha" data.

My question:
How could the VBA be modified to always delete all "tbl_" tables so that I only see the tables for the most current execution (vs. older ones for which no table was re-generated)?

Thank you,
Tom

[skydivetom]

Vlad:

Never mind the last question... I added the last five (5) lines as shown below:

Public Sub UpdateQueries()

    On Error GoTo Err_Handler

    'Declare variables
    Dim rs As DAO.Recordset, sTarget As String, sField As String
    Dim sCurrent As String, i As Integer
    Dim sSql As String, sFieldAlias As String
    Dim strBaseSQL As String
    Dim v, sOrganizationFilter As String
       
       
    'Delete all tables (prior to execution of queries)
    Dim t As TableDef
    For Each t In CurrentDb.TableDefs
        If t.Name Like "tbl_*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
    Next

[Gicu]

Looks good Tom!

Cheers,

[skydivetom]

Vlad -- quick update on the current project status. At a minimum, you deserve to hear where we/I stand on this topic.

1. I resolved all the mapping issues (scenario #1).
2. Based on the addition of the source file, all dups (scenario #2) are successfully dealt with.
3. When ultimately executing the process with actual data, I ran into many "3771" (table not found errors). However, once I realized what the underlying cause was (i.e., syncing the field names w/ the remapped field names), all worked well.

There's more work to be done on this project, I am super-thrilled that the "Generate" queries process works as envisioned. I could NOT have achieve this outcome w/o your dedicated help!

THOUSAND THANKS TO YOU... I'm very grateful your your help.

Tom

[Gicu]

Tom, thank you very much for the update, it does indeed boosts the morale...

Cheers,
Vlad

[skydivetom]

Vlad:

Forgive me for another follow-up. Everything works great assuming my source tables (PK and String) are fully aligned w/ the product table. In the past few hours, I realized there were certain fieldnames were either a) not correctly labeled in the source or product table or b) the fields did NOT exist in the source tables. For such scenario, I'm getting a bunch of 3371/3265/91 default error messages. These default messages do not provide any specific information as to which field causes the problem.

Per JPG below, is there any way I can apply brute force and NOT show the 3371/3265/91 message and ideally replace w/ a custom message? Per JPG, it would be great if custom message would include reference to the trouble field & source file. If that's too difficult, a generic message indicating a "field is missing in source table" would still preferred vs. having to ESC MS-Access.

Is that doable?

Thanks,
Tom

[Gicu]

Hi Tom,

I think rather than having a message it would be easier to have a new button on the main form to "Analyze Product Tables" and create either text file log or a local tables holding the product table name, organization and field that doesn't exist. This way you have it document it and you can go and fix them all at once instead of one at the time. What do you think?

Cheers,
Vlad

[skydivetom]

I like that... that would work for me. Is it possible to do what you suggested w/ the example where I purposefully misspelled "LastName" with "LastName_ErrorTesting" (ultimately resulting in "LastName" missing)?

[Gicu]

Yes, I'll do that later today.
Cheers,
Vlad

[skydivetom]

I appreciate it... in advance!!! Thank you, Vlad.

[Gicu]

Hi Tom,
Here you go, please let me know if it works as expected.
Cheers,

[skydivetom]

Vlad -- I love it!! This add-on "Analyzer" is simply perfect. Although I still get the errors when clicking "Generate EDW Products" before "Analyze... ", I just have to make it a habit to first analyze once the data set has been replaced w/ a newer version.

Thank you for the continued help... you are awesome.

[skydivetom]

... quick follow-up on my last post (#58). In my actual data set (where I purposefully misspelled an existing fieldname), I did NOT bring up the default errors messages when clicking "Generate EDW products" before analyzing the log/resolving the "trouble field".

In the actual data set, it processed but skipped generation of the products table. As indicated, I will have to make it a habit to first analyze PK/String version for all 5 sources before embarking to generate the products.

This will definitely work for me. Awesome!!

[Gicu]

Hi Tom,

the generating code could be modified to skip the missing fields and actually generate the tables but I feel that would not be of much value as the tables would probably be incomplete and could cause bigger problems; addressing all the issues before seems like the right approach. You could tweak the form to start with both buttons disabled, then enable just the Analyze after the organization selection in the list box and finally enable the Generate one at the end of the analyzing code. And in that (generate) code disable both again and clear the list box to force the process for the next organization.

Cheers,
Vlad