Solved

How to change field type in VB?

Posted on 2001-06-22
4
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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