?
Solved

How to change field type in VB?

Posted on 2001-06-22
4
Medium Priority
?
326 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 800 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

765 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