Auto-generate queries via VBA

[skydivetom]

Vlad -- I apologize.... another quick follow-up. When I change the "alias" fieldnames from, e.g., "X" to "Y" AND then click on the form's command button, it retains the old alias fieldnames. It appears that I need to delete the tables manually first and then I can re-recreate the tables with new alias fieldnames.



If so, does that mean the refresh does not work as envisioned? Would it be best to always DROP the tables and then re-create them? If yes, what's the most efficient way to drop all tables each time I run the "Generate ETL Products" command button?

[skydivetom]

... and another follow-up... I posted it in a new thread though:

https://www.accessforums.net/showthr...807#post476807

[Gicu]

I don't get that issue Tom, I changed the alias and the table got recreated correctly with the new name. We are dropping the tables, here is the code:

Public Sub CreateQuery(sQueryName As String, sQuerySQL As String)
Dim qdf As DAO.QueryDef, qdfMk As DAO.QueryDef


On Error Resume Next
DoCmd.DeleteObject acQuery, sQueryName
DoCmd.DeleteObject acTable, Replace(sQueryName, "qry", "tbl")  'delete existing table first


Set qdf = CurrentDb.CreateQueryDef(sQueryName, sQuerySQL)


'SELECT qryDemographics.* INTO tblDemographics FROM qryDemographics;
  
If Forms![F01_MainMenu].chkCreateMK = True Then 'STEP 3
  Set qdfMk = CurrentDb.CreateQueryDef("mk_" & sQueryName, "SELECT [" & sQueryName & "].* INTO " & Replace(sQueryName, "qry", "tbl") & " FROM [" & sQueryName & "];")
End If
    
If Forms![F01_MainMenu].chkExecuteMK = True Then 'STEP 4
    CurrentDb.Execute "mk_" & sQueryName, dbFailOnError
End If


CurrentDb.QueryDefs.Refresh
    
Application.RefreshDatabaseWindow
iCounter = iCounter + 1 'increment public table counter
End Sub

[skydivetom]

Hmh... I went back to the original file you posted. And yes, that version works fine. Ultimately, I went w/ your solution but updated a few fieldnames... nothing significant. Obviously, something must have gotten mixed up on my side. I'll go back to version 05 and redo the changes.

I'll keep you posted. Will work on it tomorrow morning.

[skydivetom]

Vlad -- quick update...

1. I went back to the original file (i.e., v05)
2. Made modifications (for my actual system) based on v05.
3. Everything works great now! Not sure what got missed in the initial update.

Anyways, I'm extremely excited about this process! As always, your help is/was greatly appreciated.

[Gicu]

Hi Tom , thanks for the update, glad to hear it's all working nice for you!

Cheers,

[skydivetom]

Vlad:

Here we go... I continue to tweak things and would like little bit more help.

It goes w/o saying, the last thing I want is to abuse your help. If preferred I will gladly open up another post.

Anyhow, please find attached my latest version. The following changes have been made:

1. This version now contains two (2) tables "00_tbl_Master". Each of them have a suffix of = "_PK" and "_String".
2. Ultimately, the data is still dummy data but illustrates views where the "STRING" version is for customer review and the "PK" version is for migration purposes. Anyhow, the data included is not entirely critical for this post.
3. Based on the PK or STRING version, I included a CASE statement in order to pass the correct conSQL parameters into the sTarget function.

... so far so good.

Additional new objects:
a. For testing purposes, I included form "Testing".
b. Upon opening the form, I can select any value from the listbox.
c. Once I click "Execute Query", the query opens with the selected organization values (Organization Alpha/Bravo/Charlie).
d. For testing only, I have linked the query to table "00_tbl_Master_PK".

Now, here's what I need some help with:
- I copied the listbox from from "Testing" onto form "F01_MainMenu"... at this time, there's no action associated w/ the listbox though.
- My goal is to ultimately enforce two value section:
1) select either "PK" or "String" from radio buttons... which is already working!!!
2) then however, user must also pick either "All" or any multiple values (Alpha or Alpha AND Bravo) from the listbox.
3) finally, once click generate command button "Generate EDW Products", I want the queries to produce the desired tables (tblDemographics and tblOther).

So, while the PK vs. STRING already works just fine, I'm not entirely sure how I can integrate the WHERE (or IN) clause into the **Set qdfMk** line so that the tables only include records based on the listbox values for organizational values (Alpha / Bravo / Charlie or all). Naturally, the WHERE (or IN) clause must be passed in association with the radio button for either PK or STRING version.

Is that possible? Can the existing code in form "Testing" be easily integrated into the code for the MAKE TABLE queries?

Thousand thanks for any additional help on this.

Cheers,
Tom

[Gicu]

Hi Tom,

Please review this updated file; again, I made some changes to the logic to make it easier to implement these new rules (having the "All" option could be confusing to users unless you add code to deselect all the others if that is selected).

Cheers,
Vlad

[skydivetom]

Vlad -- your solution is **SUPER COOL** and soo very efficient.

I continued to try to integrate this myself and somehow got (maybe) a little lucky. However, when comparing your efficient & streamlined code to mine, I only have one thing to say... I immediately trashed my inefficient version. So, thank you for providing this very elegant solution.

I only noted one thing for which I probably want to figure out a work-around. That is, upon opening the form radio button "PK" is the default value. If I were to now click on the "Generate..." command button (w/o selecting a value from the listbox), I get an error. My question: Is there a way to maybe disable the command button until a value from the listbox has been selected? Or some other method that would ensure I pick a value w/o accidentally trying to execute the queries.



Cheers -- have a great weekend!!!

Tom

[Gicu]

Hi Tom,
Glad to hear it works for you!
To deal with your latest question you might want to have a look at this thread:
https://stackoverflow.com/questions/...ns-0-ms-access
I am sure you will be able to get it work (your list box multiselect mode right now is set to Simple not Extended), but please post back if stuck .
Enjoy the weekend!
Cheers,

[skydivetom]

Vlad -- awesome... thank you for the reference. I used the following few lines of code and it works as expected now.

    If Forms!F01_MainMenu!lstSourceFile.ListIndex = -1 Then
        MsgBox "Please select a source from the listbox!", vbInformation, "Source Not Selected"
        Exit Sub
    Else
        sOrganizationFilter = Left(sOrganizationFilter, Len(sOrganizationFilter) - 1)
    End If

Cheers!!!

[skydivetom]

Vlad:

And the saga continues... as I continue to work w/ your great solution, I realize that I would love to further expand its capability.

Given this thread has been closed but you continued to help me, I do NOT want to abuse your help. Thus, I posted a new thread. In the new thread, I provided some fundamental background of history. Since you're already familiar w/ the process, please allow me to recap the additional capabilities that I'd like to include:

Testing v02A -- this version works great:
-----------------------------------------
1. First, I added one boolean field [AFSAS_MAPPED] in table [01_tbl_ProductTables]. Only fields where [AFSAS_MAPPED] = TRUE are included in "tbl_Demographics" and "tbl_Other".
2. This modification works and does NOT require tweaking.

Testing v02B -- this version required modification:
---------------------------------------------------
- In table [01_tbl_ProductTables], I have added 3 new records: PERS_NEW_FIELD1, PERS_NEW_FIELD2, PERS_NEW_FIELD3
- Here's the most critical piece of information. THESE FIELDS DO NOT EXIST IN MY SOURCE (LEGACY) TABLES [00_tbl_Master_PK] & [00_tbl_Master_String]
- Thus, given the 3 fields do NOT exists in the source tables, the make-table queries don't execute... that is, they produce zero (0) tables.

Quick background as to why I'd like to include field for which no data exists:
- Source tables [00_tbl_Master_PK] & [00_tbl_Master_String] include legacy data.
- Based on the existing solution, the source data's field names are conveted in the target system fields names... great!
- At the present time, however, I need to manually modify [tbl_Demographics] and [tbl_Other] to include the missing fields from the target system (e.g., PERS_NEW_FIELD1, PERS_NEW_FIELD2, PERS_NEW_FIELD3).
- However, if I could include all legacy fields (which I do) + the additional fields in the target system in my lookup table [01_tbl_ProductTables], it would make life sooo much easier given that I wouldn't always have to determine what "I have in legacy table" and "which fields (with null values) to be added for target tables".
- Lastly, for the 3 new (example) fields I also included the preferred data type (Short Text, Date, Double, respectively). Maybe there's a better way of doing this but ultimately, I would want to be able to designate the data type somewhere before creating the tables.

All that said, I posted the new question at the following URL.
https://www.accessforums.net/showthr...994#post476994

If there's any way you could assist w/ me tweaking the existing solution to include this new capability, I would truly appreciate it.

Thank you in advance,
Tom