Data Dictionary -- only add first field (vs. all fields) to the data dictionary

[skydivetom]

Hi:



Courtesy to Jack ("orange"), I have been using a module to create a "Data Dictionary" of all tables / each field. THIS WORKS GREAT!

For a slightly different purpose in another application, I *ONLY* would like to add the first (1st) field of each table (vs. all fields in each table).

My question: How do I modify the VBA below to obtain only the first field (vs. all fields) and add them to the data dictionary? I guess, how do I modify "For Each fld In tdf.Fields" to pull 1st field?

    With rs
        For Each tdf In dbs.TableDefs                                           'Process every table
    
            'Exclude table [Data_Dictionary] itself from being added to the data dictionary
            If Left(tdf.Name, 4) <> "Msys" _
                And Left(tdf.Name, 3) = "LK_" Then
    
                For Each fld In tdf.Fields                                      'Then for each field in each table
                    .AddNew                                                     'Add a new record in the Data_Dictionary table
                    !table_name = tdf.Name                                      'Table name
                    !Field_name = fld.Name                                      'Field name
                    !Field_type = FieldType(fld.Type)
                    .Update                                                     'Update the table with this record
                    n = n + 1                                                   'Increase counter
                
                Next fld                                                        'Get next field
            End If
        Next tdf                                                                'Get next table
    End With

[ranman256]

'1st field:
.field(0).name

[skydivetom]

Thanks for the quick response... I'm getting an error though. See attached JPG.

Am I missing anything?

[orange]

Tom,

You want to replace values in data_dictionary? With first field in each table?

Public Sub DocumentTables()
          'Requires function FieldType



          Dim db As DAO.Database
          Dim Rs As DAO.Recordset
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
          Dim strSQL_Drop As String
          Dim strSQL_Create As String
          Dim strSQL_Delete As String
          Dim BuildNew As Boolean
          'rebuild data_dictionary OR delete records and refill may/22
          ' BuildNew True          OR   BuildNew False
10        BuildNew = True 'False
          '==
          Dim idxLoop As Index

20        On Error Resume Next
30        DoCmd.Close acTable, "data_dictionary"  'it may/may not be open
          '===
          
          'SQL to Delete existing copy of this table
40        strSQL_Drop = "DROP TABLE data_dictionary;"
          
          'SQL to Create the data_dictionary table
50        strSQL_Create = "CREATE TABLE data_dictionary" & _
              "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255)," & _
              "field_name varchar(250),field_description varchar(255)," & _
              "ordinal_position NUMBER, data_type varchar(18)," & _
              "length varchar(5), default varchar(30), Reqd bit);"

60        Set db = CurrentDb()
           
70        If BuildNew Then
80            Debug.Print "-Building new data_dictionary"
90            Debug.Print strSQL_Drop
100           db.Execute strSQL_Drop, dbFailOnError
110           DoEvents
120           Debug.Print strSQL_Create
130           db.Execute strSQL_Create, dbFailOnError
140           DoEvents
150       Else
160           Debug.Print "-Empty and refill data_dictionary" & vbCrLf _
              & strSQL_Delete
              db.Execute strSQL_Delete, dbFailOnError
170       End If
180       Set Rs = db.OpenRecordset("data_dictionary")

190       With Rs
200           For Each tdf In db.TableDefs
210               If Left(tdf.name, 4) <> "Msys" _
                      And Left(tdf.name, 5) <> "Data_" _
                      And Left(tdf.name, 1) <> "~" Then
                      
220                   Debug.Print tdf.name & "   " & .Fields(0).name

230                   For Each fld In tdf.Fields
240                       .AddNew
250                       !table_name = tdf.name
260                       !table_description = tdf.Properties("description")
270                       !Field_name = fld.name
280                       !field_description = fld.Properties("description")
290                       !ordinal_position = fld.OrdinalPosition
300                       !data_type = FieldType(fld.Type)
310                       !Length = fld.Size
320                       !Default = fld.DefaultValue
330                       !Reqd = fld.Required
340                       .Update
350   Exit For '<<<<<<<<<<<<<<<<<<<<<<<<<'Jump out after first field
360                   Next
370               End If
380           Next
390       End With

400       MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"

410       Rs.Close
420       db.Close
430       Application.RefreshDatabaseWindow

Exit_Error_DocumentTables:

440       Set tdf = Nothing
450       Set Rs = Nothing
460       Set db = Nothing

470       Exit Sub

Error_DocumentTables:

480       Select Case Err.Number

              Case 3376, 3211

490               Resume Next    'Ignore error if table not found
500           Case 3270    'Property Not Found

510               Resume Next
520           Case Else

530               MsgBox Err.Number & ": " & Err.Description
540               Resume Exit_Error_DocumentTables

550       End Select

End Sub

[skydivetom]

Hi Jack:

Thanks for the update... which line(s) of code specific to only pick the 1st field?

Tom

[orange]

I provided a copy of the function. There is an ExitFor statement line 350 that limits processing to the first field in the tabledef. It should work as is. BUT if you have some customization, we'll have to work with that version.

If you need something different-- please advise.

I think all you need is to
-insert an ExitFor before Next fld

[skydivetom]

Jack:

Thank you for the prompt response... I very much appreciate it.

Attached are 2 ZIP files.

1. "Data Dictionary First Field Only.zip"
- Contains a single ACCDB include your module that you posted earlier.
- It works great and only outputs the first field of n tables into the data dictionary.

For a different process though (courtesy of Vlad C. aka #Gicu), I need to update the data dictionary based on a slightly modified process. This is where the 2nd ZIP comes into play.

2. "Custom Data Dictionary.zip"
- It includes one (1) ACCDB ("022_Custom_Data_Dictionary") in the main folder.
- It also includes two (2) source ACCDB in a subfolder.

Process for "Custom Data Dictionary"
- Upon opening, first update the two file paths in [00_tbl_AutoImportSettings]

C:\YourFilePath\LK_Source_Tables\022_LK_Mapping_NN SY_12.accdb
C:\YourFilePath\LK_Source_Tables\022_LK_Mapping_PH NSY_09.accdb

- Upon updating the file paths, click on command button Data Dictionary.

Output:
a. As of now, the data dictionary includes *all* fields based on both source files.
b. Just like in "Data Dictionary First Field Only", I only want to add the 1st fields for each LK tables in the 2 ACCDB files (in subfolder).

My question: How would I change the VBA so that I only add the first fields in the file "022_Custom_Data_Dictionary.accdb"?

Thank you,
Tom

[orange]

Tom,

You seem to have some pieces and some forms and intent that I am not following.

??What do you want and where?

??What is this? C:\YourFilePath\LK_Source_Tables\022_LK_Mapping_NN SY_12.accdb
C:\YourFilePath\LK_Source_Tables\022_LK_Mapping_PH NSY_09.accdb

You can copy the datadictionary module to the other databases and run for each database separately.

You can change the code and have datadictionary open second database(s) and record info in a table.

[skydivetom]

Jack:

- If you unzip the 2nd file, open its subfolder and copy the filepath of the subfolder.
- Next, paste the subfolder's file path into both text fields in the form OR table ... [00_tbl_AutoImportSettings]... followed by "\022_LK_Mapping_NNSY_12.accdb" and "\022_LK_Mapping_PHNSY_09.accdb", respectively.
- Then, when you execute the command button, the data dictionary (i.e., "01_tbl_Data_Dictionary_LK_Tables") will receive 30 records from all LK tables in the subfolder's ACCDB.
- I don't want to see 30 records though... I should see only 4 records though instead:

table_name
LK_AGE
LK_ALT_CASE_TYPE
LK_ALT_LOST_TIME_REMARKS
LK_GRADE

And this is why I need to update the VBA in file "022_Custom_Data_Dictionary". So, the question remains... what line(s) of code -- that exist in the working version "Data Dictionary First Field Only" -- only force the 1st field to be added to its dictionary? I then would add that additional code to "022_Custom_Data_Dictionary" in order to only import 4 table fields (vs. 30).

Hope this helps,
Tom

[orange]

As per post #6
I think all you need is to
-insert an Exit For before Next fld


The Exit For jumps out of the For Loop after the first field of a tabledef is processed.

Data dictionary as set up, performs a review of tables in the current database.

Your zip has 3 databases. Running the data dictionary on each database will create a data dictionary table in the database it runs in. 3 databases, 3 separate executions and 3 data dictionary tables. I hope you can see my confusion.

Perhaps you can describe what exactly each database is for. It is unclear if you are hoping to have 1 dictionary with the only the first field in each table in all database?

I extracted the databases from your zip into C:\users\jp\appdata\local\temp\

This is in AutoImportSettings

SettingID PRIMARY_FILE SECONDARY_FILE
1 C:\users\jp\appdata\local\temp\022_LK_Mapping_NNSY _12.accdb C:\users\jp\appdata\local\temp\022_LK_Mapping_PHNS Y_09.accdb

What are you expecting to happen at this point??
What am I suppose to do with your database names and/or subfolder?


I now get this error when stepping thru code


I need step by step instructions since the datadictionary logic has been altered.

[skydivetom]

Jack:

I apologize... the "EXIT" did the trick. I must have overlooked it.

Ultimately, by having added the LK tables primary field to the data dictionary, it now inserts links from the 2 sources to the main tables.

I will have to further customize the next process but my question (for this post) has been successfully answered! Thank you again!

Cheers,
Tom

[orange]

Tom,
Glad you have it resolved.