Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: Delete all record in table and reset the primary key

    I have a table in Microsoft Access. The table has some record.
    I want to delete all record. After all record are deleted exist a little problem. The next row not start with number 1.

    The first collumn is an auto increment primary key.
    What I need to do to delete all record from table and reset the primary key ? Please help me, I don't want to create the whole table again

    I appreciate any solution !

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    what I do is delete/cut the primary key close the table then open it and add/paste it a again
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    I have always wondered if there was a better way to do this - but then again - why would you need to?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    I only do it after doing testing ( go live of a new database) and want the number start from 1
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i thought the ONLY nice thing about surrogate keys is that they have absolutely no physical meaning ...so why do you give a damn about the value of your surrogate key?

    in your particular case, a compact and repair should get the counter back to where you want ...but i still wonder why you want to do this!

    if you want sequential numbers - there is an example in the codebank.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Here is some code that I wrote (but have not been using) to add an Autonumber field to a table. It takes the Tablename and Fieldname as parameters.

    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
    
    Public Function AddLongIntField(strTableName As String, _
                                    strFieldName As String) As Boolean
    
    'Description :  Add a field to the given table.  The field will be a long integer.
    '
    '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 fldLongInt As New DAO.Field
        
        '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 fldLongInt = tdfIncoming.CreateField(strFieldName, dbLong)
        
        'Now append the new field to the table definition
        tdfIncoming.Fields.Append fldLongInt
        
        'Set the falg to indicate the field was added successfully
        AddLongIntField = True
        
        'Cleanup
        Set tdfIncoming = Nothing
        Set fldLongInt = Nothing
        Set dbCurrent = Nothing
        
        Exit Function
        
    AddFieldError:
    
        AddLongIntField = False
        
    End Function
    All you will have to do is figure out how to drop the field and then use this code to readd it.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    Quote Originally Posted by izyrider
    i thought the ONLY nice thing about surrogate keys is that they have absolutely no physical meaning ...so why do you give a damn about the value of your surrogate key
    izy
    EXACTLY!
    Yes it looks "prettier" from your poing of view when rolling out an App to a customer... everything starts at 00001 or whatever...

    But is there reallly any need to reset the AutoNumber?
    George
    Home | Blog

Posting Permissions

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