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

    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
    Location
    The Great Land
    Posts
    52,816
    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: http://www.accessforums.net/showthread.php?t=70301 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
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    AutoNumber
    ----------------
    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

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

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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 https://www.access-programmers.co.uk...d.php?t=307883 (with attitude!)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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