Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003

    Unanswered: script to change database column size

    I have an ms access db with text field that needs to be changed from a column size of 50 to the maximum value of 255.
    Can i write a script to do this change? I want to just give the script to the dba so he can run.
    Please and thanks.

  2. #2
    Join Date
    Mar 2006
    Is this a table/form/query/report?

    When you say column size do you actually mean Field Size?

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    I spent a little time trying to do it, but was not able to. You will probably need to use DAO and the TablDef object. Here is some code I wrote to add an AutoNumber field to a tabledef. It might give you some ideas. My feeling is that unless you are doing this alot, then it is to much bother.

    Public Function AddIDField(strTableName As String, _
                               strFieldName As String) As Boolean
    'Description :  Automatically number records, beginning at 1, by adding an autonumber
    '               field to the table.
    'Parameters :   strTableName        The name of the table to add the field to
    '               strFieldName        The name to give to the AutoNumber field
    'Return :       Boolean.    Returns whether or not the field was added successfully.
    '03/18/05  Original function.
        Dim tdfIncoming As DAO.TableDef
        Dim dbCurrent As DAO.Database
        Dim fldAutoNumber As DAO.Field
        Dim idxPrimary As DAO.Index
        'Trap errors
        On Error GoTo AddFieldError
        'Set a reference to the table and create the new field
        Set dbCurrent = CurrentDb()
        Set tdfIncoming = dbCurrent.TableDefs(strTableName)
        Set fldAutoNumber = tdfIncoming.CreateField(strFieldName, dbLong)
        Set idxPrimary = tdfIncoming.CreateIndex
        'Now set the field to an auto number field
        fldAutoNumber.Attributes = dbAutoIncrField
        'Now append the new field to the table definition
        tdfIncoming.Fields.Append fldAutoNumber
        'Now create the Primary Index
        idxPrimary.Primary = True
        idxPrimary.Name = "Whatever"
        idxPrimary.Fields.Append idxPrimary.CreateField(strFieldName)
        'Now append the Primary Index
        tdfIncoming.Indexes.Append idxPrimary
        'Set the falg to indicate the field was added successfully
        AddIDField = True
        Set tdfIncoming = Nothing
        Set fldAutoNumber = Nothing
        Set idxPrimary = Nothing
        Set dbCurrent = Nothing
        Exit Function
        AddIDField = False
    End Function

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    ALTER TABLE MyTable ALTER MyColumn Text (255)
    Should do it - run with CurrentDB.Execute or DoCmd.RunSQL or paste into SQL pane of query.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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