Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Field type from code ((Access97, SR2))

    I would appreciate any help I can get in getting started with writing code to change the data type of an existing field from number to text. The table and field name will always be the same; it imports in from excel as number, and I want to be able to automatically change it to text, 5 long. TIA ! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

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

    Re: Change Field type from code ((Access97, SR2))

    You can't - the Type property of a Field object can only be changed in code when you are creating a new field, before it has been appended to the Fields collection of the TableDef object. So you can't change the field type of existing fields.

    Instead, create a table with the correct field definitions and import the Excel data into this table.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Change Field type from code ((Access97, SR2))

    Actually, you can, if you cheat by using DDL (Data Definition Language) SQL. Example:

    Public Sub TestAlterColumn()

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim strSQL As String
    strSQL = "ALTER TABLE Table1 ALTER COLUMN Field1 INTEGER"
    ' strSQL = "ALTER TABLE Table1 ALTER COLUMN Field1 TEXT(10)"

    db.Execute strSQL

    Set db = Nothing

    End Sub

    The 1st SQL statement will change Field1 in Table1 to a number (Long Integer) field. The 2nd statement (commented out in example) will change same field back to a text field, with length of 10. Be advised, however, when ran code to change field to number data type, any existing non-numeric data in Field1 was deleted with NO warning whatsoever.

    The best advice is as noted by HansV, create table with correct data types defined in first place, then import data from Excel.

    HTH

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Field type from code ((Access97, SR2))

    FANTASTIC! Thank you both, Hans and Mark! I agree with the idea of importin ginto an existing table that is already set to specification, but am glad to have this as a possible work around when needed. Thanks again!

Posting Permissions

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