Auto-generate queries via VBA (need extended capability)

[Micron]

Vlad, no I don't decompile forum db's as a rule and I didn't decompile this one although I often compile them out of curiosity. AFAIK, the code project you see can get out of sync with what is really compiled. If that means it continues to work with a missing variable but won't on another pc, there must be a hidden file or something that is missing on the new pc, whichforces a full compile when you try to run it. Just speculation on my part.



If I want to decompile:
Windows Start menu > right click > select Run & enter msaccess /decompile in run dialog
Access starts, hold shift, choose file
Close Access & reopen file and compile

[skydivetom]

Vlad -- first of all, thank you for providing a GREAT SOLUTION! The PK reference would have been a nice-to-have but I don't see it to be a firm requirement for my work. So, I think I can get by w/o identifying the "primary" reference.

I was merely curious as to why it worked for the 30 / 31 sample records, but didn't work for the 67k records. Besides the huge difference in record counts, the structure appeared to be the same. That is, I only used "primary" for 1 field within each product (demographics & other in sample data; same for actual 9 product tables).

Obviously, there may be some data in the actual source file which is resulting in the error. Initial research didn't yield any specific findings though. Anyhow, as stated before, I can do w/o the "primary" reference.

All that said, I've run into another snag... that one is independent from the code though. Specifically, our analysts conducting "legacy-to_source" mapping where the AFSAS_FIELDNAME is being duplicated. Naturally, having dups in the AFSAS_fieldname results in failure of creating the table (as you know, I can't have the same field name twice or more in a table). So, I'm currently working w/ the team to see whether or not a mistake w/ the mapping was made.

So, w/ you permission, I would like to keep this post open for little bit longer in case I have a question about a work-around requirement. Please let me know if that's ok w/ you. Otherwise, I close this thread and would open a new one (just like it did w/ this one where I cross-reference posts). I'll follow whatever works best for you.

Thank you,
Tom

[Gicu]

Sure Tom, either way is fine with me. So you are saying you have different FIELDNAME_STANDARDIZED but same AFSAS_FIELDNAME for the same product table? If both are the same (true duplicates entered in error in the product definition table) you can avoid the error by adding DISTINCT in the recordset definition in the fnCreateFields sub.

Regarding the primary key it cannot be done the way you were trying, the number of records has no relevance. Because the field is new when you add it to the table it will be null for all records already added by the make table query. So when you try to set it as a primary key you will get err 3058. If you try to populate it first with the default value supplied (1) all records will have that value, so you will get err 3022. If the fields in question would be defined as autonumbers when you add them to the table they would be populated with a sequence of unique values and setting the primary key would work OK. In the sample I've set up I have chosen PRIMARY in the index field for an existing field (Email) in the tbl_Other which I knew it hold unique values and it worked.

Cheers,
Vlad

[skydivetom]

Vlad:

In response to your most recent info, please see attached JPG and ACCDB example.

Snapshot from “Actual” Dataset:
========================
1. Please note that “INJ_INJURY_MECHANISM_TIER1_ID” is used n times in [01_tbl_ProductTables]. [AFSAS_FIELDNAME].
2. In my view, there should be a 1:1 mapping between [FIELDNAME_STANDARDIZED] AND [AFSAS_FIELDNAME].
3. However, based on the existing mapping bullet #2 is NOT true at the moment.

Current Process:
============
a. For now (testing only), I have prefixed any duplicate field with, e.g., “001_ INJ_INJURY_MECHANISM_TIER1_ID” and “002_ INJ_INJURY_MECHANISM_TIER1_ID” to ensure I have DISTINCT field names in [AFSAS_FIELDNAME].
b. In the long run, however, I have to remove the prefix of, e.g., “001” and “002”.

Follow_Up:
========
In post #18, you indicated "If both are the same (true duplicates entered in error in the product definition table) you can avoid the error by adding DISTINCT in the recordset definition in the fnCreateFields sub."

Now, presently, these duplicates were NOT entered in error. As previously mentioned, our mapping may be wrong but the analysis team suggest the 2:1 mapping. If this remains to be a requirement, it sounds like you know
of a work-around where I have the same fieldname listed twice in [AFSAS_FIELDNAME].

Personally, I still would prefer a 1:1 mapping but I simply don't know if that's doable. If so, having a work-around would be fantastic given that, e.g., "tbl_Demographics" is NOT generated when I purposely change "PERS_FIRSTNAME" to "PERS_LASTNAME" (ending up the duplicate "PERS_LASTNAME").

I'm eager to see what could be done to prevent the errors while "tbl_Demographics" is still produced.

Cheers,
Tom

[Gicu]

Hi Tom,

What I suggested as a work-around would only work for true duplicates which is not the case in your real life data. I believe what your analysts are trying to do is an attempt to normalize the data. To take your example a better way would be to replace all 6 BLS_ACCIDENT_TYPE... fields with two AFSAS fields:IN_INJURY_MECHANISM and IN_INJURY_MECHANISM_TIER (with values 1,2 or 3). This type of data manipulation is not as easy as we have been working with until now and must be thoroughly planned for. Usually you would build a script with various action queries (delete,append, update) that you would run in a set sequence to get the final data right.

I assume the two sets of three fields are mutual exclusive and come from different source files (otherwise you would lose the first value if you add a second); one way you could deal with this without changing the existing system too much is to replace the master table(s) with queries in which you get get the max of the two fields (one having a value and the other being blank). Please have a look at the update file for an example on how this would work.

Cheers,
Vlad

[skydivetom]

Vlad -- thank you for the additional & valuable feedback... I appreciate it.

I kinda figured that the M:1 legacy-to-target may pose a problem. I'll further discuss w/ the analysts. Thus, I'll keep you posted.

In the meanwhile, I had looked at the attached version you provided. What does this version exactly do? Thanks.

More to follow...

Tom

[Gicu]

Hi Tom,
The latest version I uploaded is trying to show one possible solution for the multiple-to-one legacy to target as long as they are mutual exclusive so you only have one valid (not null) value in one of the multiple fields mapped to the same target fields. Look at the code using the queries instead of the tables and inspect the two new queries (00_...) which show in the NORMALIZED_NAME either the last or first name (important note: if you have data in more than one field of the group this will not going to work but again nothing really will without further rules as you will be overwriting values).
Cheers,
Vlad

[skydivetom]

Excellent -- I'll review and do some more analysis. Will keep you posted. Thanks for all the help in the meanwhile. The current solution has already provided some significant benefit.

[skydivetom]

Vlad -- I'm getting a new error (see attached) which I previously had not experienced before.

Here's how it results:
1. Open current version of DB and execute all queries... works great.
2. Made copy of working version and THEN updated a few default values (DV) in table [01_tbl_ProductTables].[NEW_FIELD_DEFAULT_VALUE].
3. Upon changing the DVs, I ran into the error.
4. For the DV changes I made, I verified that I'm using the correct data type, e.g., "Double", "TEXT (255)", and "DATETIME", respectively.
5. Based on the review, I'm 99% confident that I did NOT miss a value and thus attempt to push a string into a date value.

Is there another reason as to why I would get this error?

Thanks,
Tom

[Gicu]

Hi Tom,

It must be one of the default values you are setting that is causing the error. Please see this post for more info:https://www.access-programmers.co.uk...r-3320.306926/

Cheers,
Vlad

[skydivetom]

Vlad:

Allow me to say first... please forgive me for asking so many questions in this post.

As previously indicated, your provided solution is *ABSOLUTELY SUPERB* and has already saved me many hours (and *prevented* a great deal of frustration). I am absolutely thankful for your help!!

W/ your permission, I'd like to go back to the "duplicate" value in the [AFSAS_FIELDNAME] one more time. I fully understand that this underlying source-to-target scenario must be addressed IOT execute the function.

In the meanwhile, I was wondering if there's any chance to change the thrown error messages (which are NOT necessarily meaningful) into a custom message.

Allow me to recap:
1. Please see attached version (this version is based on post #11).
2. Now, this version of yours works fantastic... all functions execute. Also, see top portion of the attached snapshot indicating "2 product tables have been created".

... all is well at this moment.

Now, here's the change:
a. For testing only, I purposefully duplicated one (1) value in the [AFSAS_FIELDNAME].
b. Now, when executing the command button, I get three (3) errors: 3371, 3265, and 91... see lower portion of the attached snapshot "... multiple errors".

Current process:
- Naturally, at this point in time, I know why those errors are thrown.
- Still, it would be a great improvement if I didn't get to see these 3 errors. In fact, the same 3 erros are thrown several times. Specifically, if you execute the attached ACCDB as is, you will have to click "Ok" eleven (11) times until I break out of this error loop.
- Obviously, I can perform a CTRL_ALT_DEL and use the "Task Manager" to stop this process.

Here's my question:
- Is there a way to throw one (1) custom message (e.g., "AFSAS Fieldnames are NOT distinct!") vs. encountering the 3371 | 3265 | 91 errors?
- Doing so would make this existing great product even better as the user (incl. myself) has a much better feedback mechanism to hone into the underlying cause (i.e., duplicate fieldname).


Thousand thanks in advance for the continued help!

Cheers,
Tom

[Gicu]

Hi Tom,
Have you had a chance to look at the previous sample which uses 00_qry_Master_PK to deal with the multiple sources of data being pushed into the same ASFAS field? As mentioned, as long as they are mutually exclusive then that approach would work.
As for the current 1-to-1 here is a modified example that shows you a list of issues and stops the processing.
Cheers,
Vlad

[skydivetom]

WOOOOWWWWWW!!!! This new version is totally fantastic! I **LOVE** the idea to be able to see the "list". You keep on blowing me away w/ your perfect solution to these problems.

//

Yes, I did look at the file you mentioned. As you know, repeating a fieldname is only an issue when it's in the same product. The underlying issue is that there are M:1 mapping with the same "products table" (regardless of the source). So, until I have more clarity from the analysts, I will migrate my actual data into the version you posted a moment ago (post #27). Again, I love the custom message!

Please allow me to keep this post open for little bit longer... there are a bunch of "unknown unknowns". Hope to get to the end on this validation part soon. Thank you the patience and continued help!

[skydivetom]

Oops... another follow-up. Please see post #28 first though.

//

Once I ran the procedure a 2nd time, I got the attached error. How do I ensure that the query "00_Duplicate_Error" gets deleted as well when I re-run the function. Right now, it appears I get the error because the query already exists.

[Gicu]

Sorry Tom, forgot to account for that, here you go.

Cheers,
Vlad