CODEWITHSUNDEEP
sqlms-accessinsertms-access-2016
Lou
Lou

Reputation: 2509

Inserting a value into a primary key field in Access

I have several tables that I need to contain a "Null" value, so that if another table links to that particular record, it basically gets "Nothing". All of these tables have differing numbers of records - if I stick something on the end, it gets messy trying to find the "Null" record. So I would want to perform an INSERT query to append a record that either has the value 0 for the ID field, or a fixed number like 9999999. I've tried both, and Access doesn't let me run the query because of a key violation.

The thing is, I've run the same query before, and it's worked fine. But then I had to delete all the data and re-upload it, and now that I'm trying it again, it's not working. Here is the query:

INSERT INTO [Reading] ([ReadingID], [EntryFK], [Reading], [NotTrueReading]) 
VALUES (9999999, 0, "", FALSE)

Where 9999999 is, I've also tried 0. Both queries fail because of key violations.

I know that this isn't good db design. Nonetheless, is there any way to make this work? I'm not sure why I can't do it now whereas I could do it before.

Upvotes: 0

Views: 1144

Answers (2)

Cyndi Christopher
Cyndi Christopher

Reputation: 1

If you are referring to setting a seed value for an AutoNumber Primary Key field in your target table, try this:

Create a dummy table in access with a number field having the exact same name as the primary key field in your target table, but don't make it a primary key or an auto number field in the dummy table.

Open the dummy table and enter the seed value as the first and only record in the table.

Open a query and pull in the dummy table. Click 'Append' to make it an append query and when prompted select the target table.

Pull the field from the dummy table into the query. The 'Append To' field should automatically populate with the same name as the field from your dummy table.

Run the query and close it. The value of your Auto Number field in your target table is now set to the value you entered in your dummy table and will increment up from there.

NOTE: This only works if you don't have any records in your target table.

Upvotes: 0

pod
pod

Reputation: 26

I'm not sure if I'm fully understanding the issue here, but there may be a couple of reasons why this isn't working. The biggest thing is that any sort of primary key column has to be unique for every record in your lookup table. Like you mentioned above, 0 is a pretty common value for 'unknown' so I think you're on the right track.

Does 0 or 9999999 already exist in [Reading]? If so, that could be one explanation. When you wiped the table out before, did you completely drop and recreate the table or just truncate it? Depending on how the table was set up, some databases will 'remember' all of the keys it used in the past if you simply deleted all of the data in that table and re-inserted it rather thank dropping and recreating it (that is, if I had 100 records in a table and then truncated it (or deleted those records), the next time I insert a record into that table it'll still start at 101 as its default PK value).

One thing you could do is to drop and recreate the table and set it up so that the primary key is generated by the database itself if it isn't already (aka an 'identity' type of column) and ensure that it starts at 0. Once you do that, the first record you will want to insert is your 'unknown' value (0) like so where you let the database itself handle what the ReadingID will be:

INSERT INTO [Reading] ([EntryFK], [Reading], [NotTrueReading]) VALUES (0, "", FALSE)

Then insert the rest of your data. If the other table looking up to [Reading] has a null value in the FK column, then you can always join back to [Reading] on coalesce(fk_ReadingID,0) = Reading.ReadingID.

Hope that helps in some capacity.

Upvotes: 1

Related Questions