asked on 11/14/2017

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.

Microsoft Access VBA

Last Comment

bfuchs

11/15/2017

Ryan Chong

11/14/2017

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

bfuchs

11/14/2017

ASKER

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 Chong

11/14/2017

is your Target table always having Text(255) fields?

bfuchs

11/14/2017

ASKER

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 Chong

11/14/2017

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?

bfuchs

11/14/2017

ASKER

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 Chong

11/14/2017

Right that would work if..I would not have additional fields there..

additional fields in target or source table?

bfuchs

11/14/2017

ASKER

Target.

Thanks,
Ben

Ryan Chong

11/14/2017

btw... what about the data in source table? do you want to copy it into target table?

bfuchs

11/14/2017

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

11/15/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

bfuchs

11/15/2017

ASKER

That seems to do the job.
Thank you!