Story

Inserting a row ina database is done with the INSERT INTO command line. This is quite standard SQL.

MS-Access supports the INSERT INTO instruction too. You can write macros that make use of it.

I have used it for a while and had not problems. But yesterday I discovered that there was a case when it simply wouldn't work at all. The strangest thing was that it did not work with an MS-Access database without any one error. No error at all.

I looked for a while and found something on the Internet that was saying that the problem happens when you have a column that is set to Index (No Duplicate). I had such a column so I tried to remove that setup from that column, also I'm positive, that column is not a repeat of another!

Now, imagine that you write this:

INSERT INTO <table name> (col1, col2, col3) VALUES ('a', 'b', 'c')

And absolutly nothing happens. (Yet it worked before?!)

You really scratch your head because it reports no errors and yet if you look at the table it does not get updated. A duplicate should generate an error.

But that was not my solution. The symptoms where very similar: I have a table, it works for a while and then stopped suddenly and I do not recall changing the table lately... Hmmmm...

Then I notice that I have one column always defined. I check that one column and sure enough it is marked "cannot be empty" (i.e. Allow Zero Length: No).

Problem

INSERT INTO <Table> (c1, c2, c3) VALUES (v1, v2, v3)

does not work (i.e. nothing gets inserted) without errors (totally silently, no error whatsoever!)

Solution 1

If you have a column defined as Indexed: Yes (No Duplicates), then that is most certainly the culprit. Verify that you are not trying to insert something that would generate a duplicate.

Solution 2

If you have a column marked as Allow Zero Length: No and you are trying to insert an empty value in that column, that's probably the cuprit.

Other Solutions

I did not take the time to test, but I'd bet that any similar constraint will generate the same results. So if you add a validation rule, it may fail simply because the validation fails and it would not report anything...

Got a problem with INSERT INTO telling you there is a syntax error? Check this post instead.