Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

How to change field type in VB?

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
CoStar
Asked:
CoStar
  • 3
1 Solution
 
frankyteeCommented:
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
 
frankyteeCommented:
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
 
CoStarAuthor Commented:
Thank you frankytee, this worked very well and on the first attempt. My grateful appreciation. An extra 100 points to you.
Regards
CoStar
0
 
frankyteeCommented:
you're welcome and thanks for the bonus
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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