Results 1 to 5 of 5
  1. #1
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2017

    LOCK problems resetting automumbering

    I use these lines to delete the records from a table and reset ID automumbering to start fra 1 again:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [INFOs midlertidig]"
    DoCmd.RunSQL "ALTER TABLE [INFOs midlertidig] ALTER COLUMN [Id] COUNTER(1,1);"
    DoCmd.SetWarnings True

    But the "Alter table" fails because some other use the table !

    How can I cure that problem ?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    The cure is to disregard. Why are you concerned with autonumber starting from 1? Why are you deleting all records from a table that has multiple users? Is this a split db?

    Compact & Repair will reset the seed to 1. Again, no one must be using db.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

    Also see:

    Autonumbers--What they are NOT and What They Are

    Relational Database Primary Keys

    Microsoft Access Tables: Primary Key Tips and Techniques
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    ksor's Avatar
    ksor is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Why did I get no e-mail advicing me of these replies ?

  5. #5
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Somerset, UK
    Why didn't you respond to the 2 replies?
    And if its now solved, it might help others in the future if you gave the solution used.

    Crossposted here (with attitude!)
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Resetting the Combo Box
    By gaby58 in forum Forms
    Replies: 6
    Last Post: 12-26-2017, 11:17 AM
  2. Replies: 5
    Last Post: 11-13-2017, 02:09 PM
  3. Resetting Combo Box in VBA
    By tonygg in forum Access
    Replies: 2
    Last Post: 09-06-2017, 06:26 AM
  4. resetting combo box
    By xopherira in forum Forms
    Replies: 1
    Last Post: 08-07-2015, 11:59 AM
  5. Using DSUM resetting upon next ID in dataset
    By ellisjo3 in forum Queries
    Replies: 18
    Last Post: 01-13-2014, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums