Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fields data type (Access97)

    How do I change a fields datatype in a table in code.
    I want to change 1 field from text to number (DbLong)
    Thanks,
    Scott

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 Posts

    Re: Fields data type (Access97)

    First you make a backup of your database (or if it is split of the back-end database).
    Then if you are paranoid, you make a second backup somewhere else.
    Then you go into the database container window, and open the table in design mode, and change the field type and try to save the table. If you have non-numeric data in the field (anything not numbers or periods or commas, etc.) Access will complain and say it can't save the data in the format you requested. Otherwise, it should be good to go at that point.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Fields data type (Access97)

    As you may have surmised from Wendell's reply, the answer to "How do I change a field's data type in a table in code?" is "You can't". When you change the data type interactively, Access is doing things behind the scenes you can't do in code, at least not directly.
    The Type property of a DAO Field object is read/write until the object has been appended to the Fields collection, after that it is read-only. So you can't use DAO to change the field type of an existing field.

    To simulate the interactive behaviour in code, you'd have to do the following:
    1. <LI>Create a new field of type dbLong; give it a temporary name, and append it to the Fields collection.
      <LI>Execute an update SQL instruction that sets the value of the new field that of the old field (converted from string to long integer).
      <LI>Delete the old field.
      <LI>Rename the new field to the name of the old field.
    If the text field is part of an index or relation, you'd have to do even more work...

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fields data type (Access97)

    Thanks Wendel, Hans
    After reading your replies I ended up doing something different.
    Created a temporary table to hold the data then append the data to the table with the correct datatype.
    Finally delete the temporary table.
    Thanks for your help,
    Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •