• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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.
0
bfuchs
Asked:
bfuchs
  • 6
  • 6
1 Solution
 
Ryan ChongCommented:
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 ChongCommented:
is your Target table always having Text(255) fields?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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 ChongCommented:
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
 
bfuchsAuthor Commented:
That seems to do the job.
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now