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,420
    Provided Answers: 7
    what I do is delete/cut the primary key close the table then open it and add/paste it a again
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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,420
    Provided Answers: 7
    I only do it after doing testing ( go live of a new database) and want the number start from 1
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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,434
    Provided Answers: 10
    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
  •