Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Newport, South Wales
    Posts
    8

    Arrow Unanswered: How to add an Autonumber type field to a table

    I want to add a field of 'Type' autonumber to an existing Access table but can't find a suitable 'Type' enumerator.

    See following code

    Sub AddField()
    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim prpLoop As Property
    Dim fld As Field

    Set db = CurrentDb()
    Set tdf = CurrentDb.TableDefs("Category")
    Set fld = tdf.CreateField("CategoryID", "what is AutoNumber type")
    tdf.Fields.Append fld

    End Sub

    Any help would be gratefully received

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not sure with the tabledef but you could just execute the SQL DDL:
    Code:
    ALTER Table Category ADD CategoryID AUTOINCREMENT(1, 1)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    Newport, South Wales
    Posts
    8
    Thanks, works a treat.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    A few months back I worked on this code:

    Code:
    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  DCK  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
        
        'Cleanup
        Set tdfIncoming = Nothing
        Set fldAutoNumber = Nothing
        Set idxPrimary = Nothing
        Set dbCurrent = Nothing
        
        Exit Function
        
    AddFieldError:
    
        AddIDField = False
        
    End Function
    It will create an Autonumber field. I had never thought about using ALTER TABLE. Seems a little complicated compared to one line of code. I figured I would share it anyway, in case someone found it useful.

  5. #5
    Join Date
    Feb 2004
    Location
    Newport, South Wales
    Posts
    8
    Thanks, DCK. I was working along your lines but got stuck on setting field.type and field.attributes. I'm not at home with tabledefs and fields collections. Normally do it manually but in this project I need to code a function to create lookup tables daily as part of a data conversion exercise.

    So moving on, in the next stage I need to amend a field type from Autonumber to Long integer number. Following code gives me a 3219 error.

    Public Function AmendIDField(strTableName As String, strFieldName As String) As Boolean
    'Description : Amend an autonumber field to number long integer
    '
    '
    'Parameters : strTableName The name of the table to amend
    ' strFieldName The name of the AutoNumber field
    '
    'Return : Boolean. Returns whether or not the field was amended.
    '
    Dim tdfIncoming As DAO.TableDef
    Dim dbCurrent As DAO.Database
    Dim fldAutoNumber As DAO.Field

    'Trap errors
    On Error GoTo AmendFieldError

    'Set a reference to the table and the field
    Set dbCurrent = CurrentDb()
    Set tdfIncoming = dbCurrent.TableDefs(strTableName)
    Set fldAutoNumber = tdfIncoming.Fields(strFieldName)

    'Now set the autonumber field to long integer field
    ' This is where error 3219 occurs
    fldAutoNumber.Type = dbLong
    fldAutoNumber.Attributes = 1

    'Now refresh the table definition
    tdfIncoming.Fields.Refresh

    'Set the falg to indicate the field was added successfully
    AmendIDField = True

    'Cleanup
    Set tdfIncoming = Nothing
    Set fldAutoNumber = Nothing
    Set dbCurrent = Nothing

    Exit Function

    AmendFieldError:

    AmendIDField = False
    MsgBox Err.Number & "-" & Err.Description


    End Function

    Hi Pootle Frump - Any one liners for this? Have you got a URL for SQL DDL syntax?


    PS Using Access2002 and WindowsXP

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The one liner should be something like:
    Code:
    ALTER TABLE TheTable ALTER TheColumn Long
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    This code is used to create a table using the TableDefs collection with a primary key and make it AutoNumber. Just take the bits you don't use.

    Dim db As DAO.Database
    Dim TD As DAO.TableDef
    Dim FLD As DAO.Field

    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)
    Set db = CurrentDb



    Set TD = db.CreateTableDef("myTestTable")

    'Create fields and data types
    With TD
    .Fields.Append .CreateField("MyTestField", dbText, 255)
    .Fields.Append .CreateField("Field2", dbDouble, 2)
    .Fields.Append .CreateField("Field3", dbInteger, 3)
    .Fields.Append .CreateField("Field4", dbLong)
    Set FLD = .CreateField("fldID", dbLong)

    FLD.Attributes = fldAttributes Or dbAutoIncrField
    .Fields.Append FLD
    .Fields.Append .CreateField("Field5", dbMemo)
    .Fields.Append .CreateField("Field6", dbBoolean)
    .Fields.Append .CreateField("Field7", dbCurrency)
    End With



    Set FLD = TD.CreateField("MyTestField", dbText, 255)
    FLD.DefaultValue = "default Value"


    Set idx = TD.CreateIndex("PrimaryKey")
    Set FLD = idx.CreateField("FldID", dbLong)
    idx.Fields.Append FLD
    idx.Primary = True
    TD.Indexes.Append idx

    Set idx = TD.CreateIndex("MyTestField")
    Set FLD = idx.CreateField("MyTestField")
    idx.Fields.Append FLD
    TD.Indexes.Append idx

    db.TableDefs.Append TD

  9. #9
    Join Date
    Feb 2004
    Location
    Newport, South Wales
    Posts
    8
    Thanks for links, Pootle Flump. I've skipped through these MS tutorials before but needed my current project to force me to study in greater depth. All was revealed.

    Anyway, project now completed thanks to imput from all respondents.

    Great Forum

Posting Permissions

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