Results 1 to 3 of 3

Set primary key when using make table query

  1. #1
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    May 2014
    Posts
    68

    Set primary key when using make table query

    Keeping it simple: how can I set a field to serve as the primary key of a table using a make table query? I need to run this query to update the information regularly and I don't want to have to remember to go in and reset the PK in design mode. Is there any way to set this using the query functions or in SQL mode?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Advise to avoid implementing code that routinely modifies db design. If possible, use a 'temp' table - table is permanent but records are temporary. Delete records before running process. If this is a multi-user split db, temp tables should be located in frontend.

    MAKE TABLE query cannot accomplish. Requires CREATE or ALTER sql actions or VBA using TableDefs.

    Why would you need an autonumber field in a table that has temporary life?
    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
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,337
    Agree with June. Avoid make table queries wherever possible.
    However this code will add an autonumber PK field to an existing table
    Code:
     CurrentDB.Execute "ALTER TABLE YourTableName ADD COLUMN ID COUNTER(1, 1) NOT NULL PRIMARY KEY;"
    Alternatively use a CREATE TABLE method and append the data to the new table
    For more details, see https://www.w3schools.com/sql/sql_primarykey.asp
    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. Replies: 4
    Last Post: 04-07-2018, 10:55 PM
  2. How to make a primary key on a linked table?
    By MarcoFromDowntown in forum Database Design
    Replies: 2
    Last Post: 03-04-2015, 03:19 PM
  3. Replies: 1
    Last Post: 01-18-2015, 06:02 AM
  4. MAKE TABLE QUERY maintain PRIMARY KEY
    By taimysho0 in forum Queries
    Replies: 6
    Last Post: 12-15-2011, 08:22 PM
  5. Replies: 1
    Last Post: 09-29-2010, 07:01 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
  •  
Tech Forums: Microsoft Office Forums