Solved

How to change field type in VB?

Posted on 2001-06-22
4
303 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 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