Solved

How to change field type in VB?

Posted on 2001-06-22
4
309 Views
Last Modified: 2011-09-20
I need to change the field types in another database and can't get my code to run (Runtime error '3219' - invalid operation)

Set tdf = dbs.TableDefs!Jobs  
    Set fld = tdf("Description")
        Set NewProp = fld.Properties("Type")
            NewProp.Type = dbText
            fld.Properties.Append NewProp  'Error occurs here
        Set NewProp = fld.Properties("Size")
            NewProp.Properties("Size") = 100
            fld.Properties.Append NewProp

The field is currently a Memo field and I want to change it to a text field (Size:100). Can anyone help please
0
Comment
Question by:CoStar
  • 3
4 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 6223071
i dont think you can directly change a field's type programmatically. A workaround is to create a new
field, update this new field's values from the original field, delete the original field, then rename
new field.
0
 
LVL 19

Accepted Solution

by:
frankytee earned 200 total points
ID: 6223084
sample code below. You would obviously lose any data longer than 100 characters.

Function fnChangeFieldType()

   Dim db As DATABASE, fld As Field, tdf As TableDef
   Dim sql As String
   
On Error GoTo errH
   Set db = currentdb
   Set tdf = db.TableDefs("Jobs")
   'create new field
   Set fld = tdf.CreateField("NewDescription", dbText, 100)
   tdf.Fields.Append fld
   tdf.Fields.Refresh

   'need to insert values from original field to new field
   sql = "UPDATE Jobs SET NewDescription = Description"
   db.Execute sql
   'now delete original field
   tdf.Fields.Delete ("Description")
   tdf.Fields.Refresh
   'rename new field to original field
   Set fld = tdf.Fields("NewDescription")
   fld.name = "Description"
   tdf.Fields.Refresh
   Set db = Nothing
   Exit Function
   
errH:
   MsgBox Err & " " & Err.Description
   
   
End Function
0
 

Author Comment

by:CoStar
ID: 6223141
Thank you frankytee, this worked very well and on the first attempt. My grateful appreciation. An extra 100 points to you.
Regards
CoStar
0
 
LVL 19

Expert Comment

by:frankytee
ID: 6223242
you're welcome and thanks for the bonus
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question