MS Access unable to set primary key on make table query

Question

I am using MS Access 2016, I've got a table in my database that is linked to an external source through an ODBC connection. Data is come from MS excel. I created Query and linked up to macro. It runs correctly but it delete the previous table and remove primary key that i set. Here is my Sql Query:

SELECT AllStaff.NAMEID, AllStaff.USERNAME, AllStaff.DEPARTMEN,
    AllStaff.Lastname, AllStaff.Firstname, AllStaff.RFCARDNUMB, 
    AllStaff.[PERSONAL R], AllStaff.VENDOR INTO AllStaff1
FROM AllStaff
WHERE (((AllStaff.NAMEID) Is Not Null));

So i want to set NAMEID as a primary key it will remain primary key after adding values. Thanks in advance

Answer 1

You need INSERT INTO ... SELECT, not SELECT ... INTO

Official syntax: INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase] SELECT [source.]field1[, field2[, …] FROM tableexpression

Try:

INSERT INTO AllStaff1 (AllStaff.NAMEID, AllStaff.USERNAME, AllStaff.DEPARTMEN, 
    AllStaff.Lastname, AllStaff.Firstname, AllStaff.RFCARDNUMB, AllStaff.[PERSONAL R], 
    AllStaff.VENDOR)
select AllStaff.NAMEID, AllStaff.USERNAME, AllStaff.DEPARTMEN, AllStaff.Lastname, 
    AllStaff.Firstname, AllStaff.RFCARDNUMB, AllStaff.[PERSONAL R], AllStaff.VENDOR
FROM AllStaff
WHERE (((AllStaff.NAMEID) Is Not Null));

This won't delete the table, and should keep your primary key. Warning from the MS link above:

If your destination table contains a primary key, make sure you append unique, non-Null values to the primary key field or fields; if you do not, the Microsoft Access database engine will not append the records.