How to change programatically all fields of a table, according to another table?

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

I have two similar tables, one is a local table and the other one is a linked table.
I would like to change all fields with exact name to be the same datatype as the other.

So lets say Table1 has following Fields,  Field1 Text, Field2 Memo, Field3 Memo.
I would like some code that should change Table2 Field1 to text (255).

PS. In my case its either memo or text 255, and changes are from memo to text only, not vise versa.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
So lets say Table1 has following Fields,  Field1 Text, Field2 Memo, Field3 Memo.
I would like some code that should change Table2 Field1 to text (255).

try execute the Alter Table statement?
ALTER TABLE yourTable ALTER COLUMN yourMemoField TEXT(255)

Open in new window


ALTER TABLE Statement
https://support.office.com/en-us/article/ALTER-TABLE-Statement-81D241E3-1522-4103-ACF0-9857888D581C
Hi Ryan,

What I'm trying to avoid is having to manually compare both tables with many fields to see if one's datatype changed..
Perhaps someone went thru this already and would share the code.
Just a time saver..

Thanks,
Ben
Ryan ChongSoftware Team Lead

Commented:
is your Target table always having Text(255) fields?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You mean the table I'm looking to update? No.
I have fields of different types.
However the current task is just to find out which fields are to be changed from Memo to Text 255.

Thanks,
Ben
Ryan ChongSoftware Team Lead

Commented:
I kinda don't get your requirements.

if your source table can contains Memo or Text fields (this is fine) and then you want to migrate the data over with field type as Text fields?

IF you want them to be EXACTLY the same, you can simply drop your target table, and then re-create it using SELECT ... INTO .. statement. Would it make sense?
The source table changed some fields from Memo to Text 255, and therefore I need to change the destination as well.
and then re-create it using SELECT ... INTO .. statement. Would it make sense?
Right that would work if..I would not have additional fields there..and data which I may need to keep.

Thanks,
Ben
Ryan ChongSoftware Team Lead

Commented:
Right that would work if..I would not have additional fields there..
additional fields in target or source table?
Target.

Thanks,
Ben
Ryan ChongSoftware Team Lead

Commented:
btw... what about the data in source table? do you want to copy it into target table?
Hi Ryan,

This is a table which is being downloaded from a web app, and I have append query to load all new records into a local table.

What I was looking is for a code that loops for all fields of Table2 and checks if datatype is not the same as the same named field in table1 then execute the update statement you originally posted..

Thanks,
Ben
Software Team Lead
Commented:
changing the table structure with data available inside that table could be at risk for data lost or data errors.

anyway, you can try this in order to change the field's data type of target table by referring to source table. I would compare them with field names, something like below:

Function ChangeFieldType(ByVal tableSource As String, ByVal tableTarget As String, Optional ByVal newType As String = "TEXT(255)") As Boolean
    On Error GoTo Err
    Dim tblSource As TableDef, tblTarget As TableDef, fld_s As Field, fld_t As Field
    Dim sql As String
    
    Set tblSource = DBEngine(0)(0).TableDefs(tableSource)
    Set tblTarget = DBEngine(0)(0).TableDefs(tableTarget)
    For Each fld_s In tblTarget.Fields
        For Each fld_t In tblSource.Fields
            If fld_t.Name = fld_s.Name And (fld_s.Type = DataTypeEnum.dbText Or fld_s.Type = DataTypeEnum.dbMemo) Then
                sql = "ALTER TABLE " & tblTarget.Name & " ALTER COLUMN " & fld_t.Name & " " & newType
                Debug.Print sql
                CurrentDb.Execute sql
            End If
        Next
    Next
    ChangeFieldType = True
    Exit Function
Err:
    MsgBox Err.Number & ":" & Err.Description, vbCritical, "Error"
    ChangeFieldType = False
End Function

Open in new window


call it like:

1. change target matched fields to Memo
ChangeFieldType("Table1", "Table2", "MEMO")

Open in new window


2. change target matched fields to text

ChangeFieldType("Table1", "Table2", "TEXT(255)")

Open in new window

That seems to do the job.
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial