VBA Data Import Routine (multipe XLS into Access) with duplicate fields/values into DISTINCT tables

[orange]

Here's another that allows you to keep the numeric fields as numbers.
It wasn't as difficult as I thought.

It should work with your nickname test.


That's it for tonight.
Good luck.

[skydivetom]

Orange -- I just saw the updated version with "numeric". I'll give it a shot and add another numeric field.

WRT to "Nickname"; I created a new field (ShortText) in Org_C; I then opened the module "DataDictionary" and ran it. Afterwards, I ran "Module1". Unfortunately, [Nickname] was not listed in table "data_dictionary" nor was the associated "LK_Nickname" created. What am I missing?

Based on the "Nickname" scenario, I also would like to have the following flexibility:
- New XLS (source) files will/may be added
- As part of the *potential* new source files, the majority of field/field names will overlap; however, there's always a chance that, e.g., source file "Org_D" includes new fields such as "Address" AND "DateCreated"... well, ultimately, that's the "Nickname" scenario.
- So, it would be great if dictionary will easily *recognize* new fields and accommodate most data types (short text, long text, integer, float, date, Boolean, currency)

Finally, when reading through the module DataDictionary, there are many functions included. Are all of these required for the DataDictionary process or are some based on your previous application and could be removed from this LK_creation process? I try to limit the amount of VBA that I have to review/maintain.

Cheers,
Tom

[skydivetom]

Good morning, Orange:

First of all, thank you for the continued assistance... I very much appreciate it.

Earlier this AM, I already posted some additional info/question. Now, after spending it little more time w/ the code/procedures, I'm coming across some odd behavior. Hopefully, I capture the issue (steps) correctly.

- First, I changed the tables names from Org_A, Org_B, Org_C to tbl_Source1, tbl_Source2, tbl_Source3.
- Next, I re-ran the DataDictionary function.
- Then, I re-ran the LK tables function.
- Next, I reviewed the LK table "FirstName"... in here, it now had only 27 unique names. These 27 names originated from original table name Org_A. However, LK_Firstname did NOT include the additional 46 distinct first names from originally Org_B.

So, I am not following the logic as to why it only took the 27 distinct first names from tbl_Source1 (aka Org_A), vs. the distinct value of 66 (combination of tbl_Source1 AND tbl_Source2)...

- Next, I copied tbl_Source1 (both structure and records) and named it tbl_Source4 (a new table). Then, I changed one first name to "Zacharias" (a first name not found in the other 2 tables).
- I then re-ran the data dictionary to ensure the new source table (4) is added; this was followed by running the LK tables.

And here is the peculiar outcome. At this point, "LK_Fistname" included 68 records (incl. "Zacharias")... this is the correct # for this test data set.

My question:
Why would I get 27 (vs. 66) first names when changing the table names BEFORE creating the 4th table? However, AFTER creating the 4th table and adding 1 new distinct value, I'm getting 67 distinct first names. Is there a certain order when changing/adding tables names to ensure the data dictionary is updated correctly?

Ultimately, it's easy to determine when using this test data set. However, when working w/ real data (thousands of records), it won't be as easy. Naturally, I must feel comfortable that I'm not excluding values simply because I didn't run the routines in correct order.

Any thoughts/recommendations?

Thank you!!!

[orange]

Good morning.

The only thing I can suggest for your question re 27 vs 66 is procedure. Whenever there is a change to any table/field, you must run the data dictionary.

Perhaps you can send some more test data that spans the anticipated real data. I'll look at the issue in a little more detail.
I did a test late last night --added a nickname to some records in OrgA and reran data dictionary then got an LF_nickname table.
But will recheck your Zacharias type issue as well.

Update: I did get the 27 vs 66 issue, but not sure of the source. Still investigating.

[skydivetom]

Orange -- ok... I went back to original version (w/ numeric) you posted yesterday.

I re-ran the LK tables w/o having made any changes whatsoever.

And to my surprise, the Lk for first names included only 27 records. Thus, I presume that the distinct values from Org-B were never added to begin with.

Is there anything else that might require code mods given I don't grab all distinct values from all existing source tables?

Thank you again!

[orange]

Tom,

I'm not sure at the moment. It sure seems that OrgB firstnames are not being added.
I have code for the error 3022 attempting to add a duplicate that seems to be working.

If I run the generated sql for firstnames seperately
-OrgA loads fine 27 records
-OrgB gives me about to add 45 records.
then this



a 3022 Key violation which I understand for 6 records. It also indicates some issue causing the yes, No screen.

So 27 (OrgA) + 45 - 6 (OrgB) = 66 (6 duplicates not dded)
When I click yes I get the 66 records


There must be another error condition that I have to trap and ignore.

[orange]

Tom,

I have not found a solution, but have asked for suggestions from others.
I have tried changing from unique index to Primary Key, but got same result.
I have not seen this issue previously.

[skydivetom]

Orange - while I'm glad to learn that you could replicate the data processing issue, I'm hopeful that this issue can be fixed.

Particularly since I had a brief discussion w/ the team this morning and shared (prior to realizing the issue) the "dummy solution". So, it goes w/o saying that I would really like this automated LK generation/process to work.

At any rate, sounds like we're currently in a "pause" until this issue is resolved. That is, at the present time, I execute the "Data Dictionary" function AND the "CreateLKTables" function. Once they executed, I want to feel comfortable that no "behind the scene" errors prevented from loading all tables.

I standby for additional guidance/ideas that you may be willing to share. THOUSAND THANK YOU!!!!

[orange]

Tom,
Update. I am attaching another version. This one runs from Form frmTest.
It now uses PK rather than unique indexes. Also, I have removed the "culprit" parameter dbFailOnError
which was removing all additions when a duplicate was encountered. My thanks to colleagues who pointed me to the solution.
I have tested it with a copy of OrgA as OrgD and changed 1 firstname to Zacharias. I had already put some NickNames in OrgA.

So test this version --that deals with text or number datatype. As above, you can run this from a form.

If you have more data and datatypes, we can look at that.

Let me know how you progress.

[skydivetom]

Orange:

Wow... based on the testing I've completed thus far, this version seems to do exactly what I was hoping to achieve.

First of all, using the form makes the process sooo much easier (and guarantees the correct order of executive -- as long as cmdButtons are clicked top/down).

Here's what I've done for testing:
1. Changed OrgA-OrgD table names to tbl_Source1 through tblSource_4. All executed w/o any issues (just wanted to make sure that procedures does NOT look for table prefix "Org"). Worked great!
2. Copied tblSource_4 and called it tblSource_5. Changed last record's name from "Alen" to "Xavier". Based on the previous (27 vs. 66 scenario, and 67 with "Zacharias"), I hoped that the new LK_Firstname table would show 68 records (again, due to Xavier). It did... great!
3. Removed all existing [Nicknames] from multiple tables and created new [Nickname] field in table tblSource_3. This table has 20 records but I added 2 dups. The new distinct Nickname value count = 18. Perfect!

I'll continue to test other scenario with other (previously not used) data types, etc. I'm hopeful those "tests" will also be successful.

As I'm confident that I won't see any new/unforeseen issues, THIS SOLUTION IS *** BRILLIANT *** !!!

In the meanwhile, please allow me to ask the following.. the module for the DataDictionary uses 15 functions/subs, etc. My question: Are all of these 15 functions/sub required to update the data dictionary? If not, are there any functions that could be removed from the module **without** interfering w/ the larger goal of this LK Table method? If there are any which have nothing to do w/ the data dictionary (or any required sub routines), which ones could be removed/deleted? Just asking to become more familiar w/ the code.

Public Sub DocumentTables
Private Function FieldType
Sub fjListIndexesToTable
Sub FindQuery
Sub ContainerObjectX
Sub TestDBContainerProperties
Sub GetRecordCounts
Sub smig
Function assignTableDescription
Sub smig2
Public Function docForms
Sub GetTableIndexProperties
Sub RequiredX
Sub RequiredOutput
Sub testQryDesc()

But for now, I thank you Orange (as well as all those experts who helped w/ the latest issue) sooo very much for providing a perfect solution.

Cheers,
Tom

[orange]

Tom,

For your use of DataDictionary you only need:
Public Sub DocumentTables
Private Function FieldType

The others have been used to assist people or get certain details from time to time.
You can remove them from your DataDictionary module.

I will investigate other datatypes, but not all datatypes will be acceptable. Things like attachments, multivalued fields etc, but I don't think that should affect your use.

Do you have a plan for using these LK_ tables??

[orange]

Tom,

Here is a version to deal with a few datatypes.( text,long,double,currency)
It gives a custom error if it encounters Memo or other datatype.

Again test and review.

Good luck with your project.

[skydivetom]

Copy on the 2 required functions... that'll make it easier for me to figure out "what's doing what". Thank you.

Also, I will check out the latest version you posted. More than likely I won't get to this evening, but definitely first thing tomorrow morning.

In the meanwhile, please allow me to run by another thought. This is NOT a new requirement, but I may neglected to mention it during the original post given there were only a few distinct fields.

So, background first:
- Let's say the actual number of fields across the various organizations = 200
- Now, out of the 200 fields, there will be some duplicate fields; thus, the distinct # of fields may = 150.
- Based on the existing routine (and I haven't run it w/ the actual data yet; I was still doing the testing w/ sample data), 150 LK tables would be created.
- Now, in reality, I may not need a lookup table for every single field. That is, I may have multiple Boolean fields and no lookup from "old" to "new" is required.
- So, let's say, that I truly end up with only 100 (out of the 150 distinct) fields that require a lookup table.

Based on the above, I could envision to add a table as illustrated in the attached JPG. So, if the 2nd column shows and "X" (or is marked as TRUE) is there a way to use the 2nd column "flag" as a trigger for the LK tables to be created? So, in this basic example, I'd only end up w/ 4 LK tables (vs. 12 LK tables).

I hope this wouldn't throw a complete "monkey wrench" in the existing procedures. If it can't be automated, I naturally could always delete those 8 LK tables. At the same time, it would be a bit harder w/ reality given I'd have to ID those, e.g., 100 LK tables that should be deleted.

Any thoughts on how this simple matrix could be utilized for further ID of LKs? And, btw, if you prefer me to open a new thread (w/ link from this one), I'll gladly do so.

Cheers,
Tom

[orange]

I'm not sure what "other" might be. But you could easily create a routine to delete whatever LK_ tables you don't need. The routine that exists should build a table for each unique field name. It could be adjusted to not build for specifically identified fields. The delete approach might be easier. Also, just because the table prefix is LK_ doesn't mean it can't be used for other purposes.

Did you watch the video by Alec? In reality there are often "glitches" when different existing applications are merged (company take over; departmental mergers etc). Some things like Client, Job, Owner need a lot of reconciliation because of the different environments, businesses, management styles etc. It is better to have a high level model to help bring things together. I've been in silo situations with their inherent duplication, and in corporate database where there was a discipline in managing
data/information. We had dictionaries and models and standards, and it still was a struggle. We went through multiple departmental restructures and mergers. I've been retired several years.

Do you need more info on the DocumentTables/DataDictionary materials? It's a fairly old tool/utility I did years ago that was always handy. Those other routines in that module were just where I happened to be when helping someone or trying something. It just seemed a good place to store them at the time.

My earlier question about your use of the LK_ tables was to get some context of where the LK tables fit into resolving/reconciling differences in definition, representation and usage of the data.

I'm thinking of one of our applications where we had a corporate Establishment database. Establishment could be Customer, Vendor, Supplier, Fabricator, Loan Recipient.. each with Name (Legal, Operating, Alternate/Alias)... and each could have multiple addresses (Physical Location, Mailing, Shipping/Receiving
So when dealing with companies and vetting information several attributes were brought together to help uniquely identify who was who. All that to say that having a list of unique values of each of the fields is a start, but it has to fit within some scheme (that all participants agree on [at least to some extent]).

[skydivetom]

Orange:

You provided lots of great information. Glad to hear that the delete routine should be an easy fix. And, as you pointed out, maybe I won't delete those tables after all. Something I'll be thinking about in the coming days.

And, yes, I truly enjoyed the video by Alec and Juha. I truly enjoyed Alec's opening remarks... he really drove the point home to never stop asking questions IOT learn about the system.

You indicated whether or not I needed more info on the DocumentTables/DataDictionary. I'd always welcome to learn more/have a better understanding once a good number of comments are added to the VBA. That usually helps clarifying things for me.

With respect to the LK tables, I'll definitely put them to use (through this utility). We just have a good number of flat files that need to be organized (incl. data value transformation). Being able to now "catch" every distinct value in the source files, we should be able to now add the target system values into the LK tables and then begin value transformation.

Again, thank you so much your help. Your tool/solution is absolutely amazing!