Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: Altering field size in Access

    Hi,

    I'm trying to change a field size property in an Access 2000 table using some VBA code. However when I try to set the Field size to Decimal using DAO/ VBA my code errors and fails to make the change. Any ideas on how I can fix this? Here is the code I am trying to use:



    Sub Reformat_Numbers()


    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set db = CurrentDb

    'set table name
    Set tdf = db.TableDefs("Primary_table")

    'sets field name
    Set fld = tdf.Fields("LINENET")

    'goes into event to change field size to Decimal
    SetFieldProperty fld, "Field size", dbText, "Decimal"

    db.Close

    End Sub



    Public Sub SetFieldProperty(ByVal fld As DAO.Field, ByVal strPropertyName As String, ByVal iDataType As DAO.DataTypeEnum, ByVal vValue As Variant)
    Dim prp As DAO.Property

    Set prp = Nothing

    On Error Resume Next
    Set prp = fld.Properties(strPropertyName)
    On Error GoTo 0

    If prp Is Nothing Then
    Set prp = fld.CreateProperty(strPropertyName, iDataType, vValue)
    fld.Properties.Append prp
    Else: prp.Value = vValue
    End If

    prp = Nothing
    End Sub

  2. #2
    Join Date
    Aug 2003
    Posts
    31
    Cant you just goto the table's design view, click on the field and then change the field size that way.

    I have no idea how to do it in SQL though.

  3. #3
    Join Date
    Oct 2003
    Posts
    9
    Unfortunately it needs to work automatically

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try to use the ALTER TABLE

    ALTER TABLE Tabelle {ADD {COLUMN Feld Typ[(Größe)] [NOT NULL] [CONSTRAINT Index] |
    CONSTRAINT Mehrfelderindex} |
    DROP {COLUMN Feld I CONSTRAINT Indexname } }

  5. #5
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Will you be altering table design automatically all the time or it is something that will happen at the time of application installation.

    Changing table design frequently is definitely a bad idea.

    Originally posted by ChrisD
    Unfortunately it needs to work automatically

  6. #6
    Join Date
    Oct 2003
    Posts
    9
    To be honest I could probably get away with just adding a column using an Alter table but I somehow need make sure that the field size is set to decimal. I'm trying this but it doesn't work. Any ideas?

    ALTER TABLE Primary_table ALTER COLUMN Linenet NUMBER (decimal)

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    I think the Alter Table is suitable for your purpose

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    However when I try to set the Field size to Decimal
    forgive my ignorance, but what is "decimal"?
    i know single & double - decimal is new for me

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Oct 2003
    Posts
    9
    Decimal is used more often than not with currency values.

    Sadly I can't get Alter table to work. Anyone got an example of adding a column with the field size set to decimal? I would be VERY gratefull

Posting Permissions

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