ms-access DoCmd.RunSQL statement can't add record due to key violation but no records are in table

Question

I have been running this query for a about 3 weeks now and all of a sudden it stopped working. This is the error I get:

My table is completely empty. Here is the code for the INSERT INTO statement:

SQL$ = ""
SQL$ = SQL$ & " INSERT INTO table1"
SQL$ = SQL$ & " VALUES ('test', 1, '1-1', #01/01/2001#, 1000, 'testing', 'example', 1, 2, 30);

DoCmd.RunSQL SQL$

There is nothing that should be violating the primary key value since there are no records existing. Is there another way to violate the primary key or is this an access issue?

This is the table design:

Ignore the bad field names, I had to change them temporarily so I could post here.

I even tried to run this as an Access query outside of VBA:

SELECT * FROM table1

and it returned nothing.

UPDATE:

This is interesting. So table1 is in a 1 to many relationship, the vNum being the many in table1 as stated earlier. On the 1 side, call the table "oTable", it already had a list of vNum's that I imported from a different database. I recently created a new record in oTable with the vNum called "vTester". In my form for table1 that is generating the above SQL insert statement, I can choose whichever vNum I want from a combobox. The user CANNOT choose one that doesn't exist. In my form I picked "vTester" and got the above errors. When I chose one of the imported vNum's however, the SQL command worked. Any ideas?

Please post a screenshot of table1 design view. If it has an autoincrementing identity field (key), you might be implicitly attempting to write col1 string into it. To avoid that, specify column names in your SQL: INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...); — Stack
It is almost impossible to answer this question without knowing both the table design, and the values that you are trying to insert. — ste-fu
I updated my question. @StackExchanger I tried that but still had the same probelm — Michael
The message doesn't say 'primary key violation', it says 'key violation'. My guess is that one of your fields has a relationship to a field in another table. That table must already contain the value that you are trying to insert into the related field. — AVG
No, there is a problem. If this were an order table with a reference to the customer number in a customer table, you can't add an order with a customer number that doesn't exist there. — Andre

Michael Michael · Accepted Answer · 2016-08-23T15:42:36

After a long time of avoiding entering in a new record, I repaired and compacted the database and tried again and it works. Sometimes Access is finnicky like that for me and just needs a fresh(ish) start. Hasn't happened again since!

ms-access DoCmd.RunSQL statement can't add record due to key violation but no records are in table

1 Answers