VBA to automatically insert 4 new fields for n tables in the DB

[skydivetom]

Experts:

I have a question WRT to **automating modifications** to a range of n tables.

Background:
- I have a database that contains 70 Lookup (tables). The attached "Sample DB" includes 3 tables.
- Each table has the prefix "LK_" followed by a distinct name. For example, I have "LK_Age" and "LK_Gender".
- Each table contains currently one (1) field name. Naturally, the name of the fields are different. So, "LK_Age" has one (1) field called [Age] while "LK_Gender" has one (1) field called [Gender].

Here's what I need some help with:
- I need to *automatically* insert (ideally via automated VBA routines) 4 fields into the *each* table. Those three (3) field are: [Target_Table], [Target_Field], [Target_PK_FK], [Comment]
- Data type of the to be inserted field 1st field [Target_Table] will always = "Short Text"
- Data type of the to be inserted field 2nd field [Target_Field] depends on the data of the existing field. See ** below for details.
- Data type of the to be inserted field 3rd field [Target_PK_FK] will always = "Number"
- Data type of the to be inserted field 4th field [Comment] will always = "Short Text"

Dynamic creation of data types for the 2nd field to be inserted:
** For example, for "LK_Age", [Age] = NUM; thus, [Target_Field] must always = "NUM"
** Alternatively, for "LK_Gender", [Gender] = TEXT; thus, [Target_Field] must always = "TEXT"
** Naturally, if another existing field would = "Date", the to be inserted field must also have a "Date" data type, and so forth



Please see attached the tables structure "before" and "after" the VBA was run.

My question:
- How can I loop through n tables and automatically insert 4 new fields where the 1st and 4th fields will always have a data type = "Text" AND the 3rd field will always = "NUM"? However, the data type of the 2nd field to be inserted is based on the data type of the existing field?

Please see attached sample DB with some existing Lookup tables.

Thank you,
Tom

[ranman256]

normally ,one has no need to program making fields.
The fields are made in advance simply by clicking table design and entering them,
or copy/paste them.

[skydivetom]

Ranman -- copy that.

Given the current # of 70+ fields, I was hoping to automate the monotone copy/pasting effort of 70*4 (280). Potentially, the number of fields could even grow to more than 200... in that case, I would have to copy/paste field name more than 800 times.

If that's not doable, then "ok". However, if it could be programmed, it certainly would mitigate potential errors when selecting the correct data type for the 2nd field.

Thank you!

Tom

[Minty]

You can create tables on the fly by inserting the right data types into them using a

SELECT field1, field2, field3, field_Etc INTO YourNewTableName FROM SourceTableName.

This may well be the quickest route to solving your problem.

Edit: You can also determine the datatype using the TableDef properties for the source table.

[orange]

Tom,

These could be added/built-in with the process to create each of the TL* tables.

[skydivetom]

Jack -- yes, including those 4 fields (w/ empty values for each LK_ table) from the "get-go" would be the most optimal solution. Assumption is that those 4 new fields (empty) would NOT have an impact on the data dictionary (existing fields).

I wouldn't know where to begin modifying the current module(s).

Thanks,
Tom

[skydivetom]

And, btw, to make it easier, maybe the 2nd field does NOT necessarily have to match the data type of the existing field. After some more thinking, just because the source data = TEXT, does NOT mean the target must = TEXT... in fact, there's a good chance that it may be a NUM. To get started though, it may be ok to make all fields = TEXT.

[accesstos]

And, btw, to make it easier, maybe the 2nd field does NOT necessarily have to match the data type of the existing field. After some more thinking, just because the source data = TEXT, does NOT mean the target must = TEXT... in fact, there's a good chance that it may be a NUM. To get started though, it may be ok to make all fields = TEXT.

Tom,

It is easier to convert a number or a date to text rather that the opposite.

Put this subroutine in a module and run it:

Sub AddLKFields()
    Dim rsLK As DAO.Recordset
    Dim db As DAO.Database
    Dim fld As DAO.Field

    Set db = CurrentDb
    Set rsLK = db.OpenRecordset("SELECT [Name] AS table_name " _
                                & "FROM MSysObjects " _
                                & "WHERE [Name] Like 'LK_*';" _
                                , dbOpenForwardOnly)
    With rsLK
        If Not (rsLK.BOF And rsLK.EOF) Then
            On Error Resume Next
            While Not rsLK.EOF
                With db.TableDefs(rsLK![table_name]).Fields
                    'Add [Target_Table] (Text)
                    Set fld = New DAO.Field
                    fld.Name = "Target_Table"
                    fld.Type = dbText
                    .Append fld
                    'Add [Target_Field] (Same Type as the first field)
                    Set fld = New DAO.Field
                    fld.Name = "Target_Field"
                    fld.Type = .Item(0).Type
                    .Append fld
                    'Add [Target_PK_FK] (Long)
                    Set fld = New DAO.Field
                    fld.Name = "Target_PK_FK"
                    fld.Type = dbLong
                    .Append fld
                    'Add [Comment] (Text)
                    Set fld = New DAO.Field
                    fld.Name = "Comment"
                    fld.Type = dbText
                    .Append fld
                    'Refresh fields collection.
                    .Refresh
                End With
                rsLK.MoveNext
            Wend
        End If
    End With
    rsLK.Close
    Set rsLK = Nothing
    Set fld = Nothing
    Set db = Nothing
    On Error GoTo 0
End Sub

[skydivetom]

John:

3 words... BRILLIANT, BRILLIANT, BRILLIANT!

I'm gonna try to see how to add a counter (in msgbox) indicating e.g., "Success -- " & n & " tables were modified and now include the 4 target fields!"

As always, much obliged for yet another PERFECT SOLUTION!

[orange]

Tom,

I responded in the other thread.

[accesstos]

John:

3 words... BRILLIANT, BRILLIANT, BRILLIANT!

I'm gonna try to see how to add a counter (in msgbox) indicating e.g., "Success -- " & n & " tables were modified and now include the 4 target fields!"

As always, much obliged for yet another PERFECT SOLUTION!

Tom, you are welcome!
As always, it was my pleasure, and I am glad to hear that it worked.

About the counter, you can compare the Count of Fields before the addition and after. If differs, that means that something has done.

Cheers,
John

[skydivetom]

Yup... everything is working out as intended. Superb!

Kalispera!!

[accesstos]

Yup... everything is working out as intended. Superb!

Kalispera!!

Very nice!

Now, I can say in my turn,...

Καληνύχτα!

[skydivetom]

John -- I posted a new related thread at the following URL
https://www.accessforums.net/showthr...983#post473983

Your solutions (just like Jack's and moke123's) were always very, very impressive. Any thoughts would be greatly appreciated.

Cheers,
Tom