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

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.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
0
bfuchsAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
is your Target table always having Text(255) fields?
0
Determine the Perfect Price for Your IT Services

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

bfuchsAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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?
0
bfuchsAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Right that would work if..I would not have additional fields there..
additional fields in target or source table?
0
bfuchsAuthor Commented:
Target.

Thanks,
Ben
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
btw... what about the data in source table? do you want to copy it into target table?
0
bfuchsAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
That seems to do the job.
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.