3

I am trying to insert 2 values from variables into a table via SQL, the code finishes without error but the entries are not showing up in the table.

I tried executing the code within the immidate window but that gave me an error about my brackets (Don't really know how to enter the prompt properly there), so I changed my query from having variables to having set values to insert, but it still does not work nor does it give an error.

I made sure I had no "On Error" in my code and tried to build in an error manually. Syntax errors are shown as usual so I am assuming that everything is fine with error messages.

'Table Columns: ID, ProjectID, Versionnumber

SQL = "INSERT INTO tblVersion " & _
"(ProjectID, Versionnumber) " & _
"VALUES (1, 'v3.0');"
Currentdb.Execute SQL

I expect the Value to be shown when I open the table. Instead, nothing happened at all.

Improve this question
4
  • Try to add a space after Versionnumber) Commented Apr 8, 2019 at 12:18
  • 4
    If you want errors, you need to use Currentdb.Execute SQL, dbFailOnError. Else, any error will cause a silent fail. Commented Apr 8, 2019 at 12:19
  • I tried, but the result does not change. Will correct it in the code segment Commented Apr 8, 2019 at 12:20
  • It shows errors now and I can solve the Problem. Thank you @ErikA Commented Apr 8, 2019 at 12:22

1 Answer 1

Reset to default
3

CurrentDb.Execute doesn't raise all errors by default. It will just silently fail, without letting you know the query was unsuccessful, independent of any On Error statements.

As far as I know, all errors that can be generated at compile time (syntax errors, invalid table names or field names) do get raised, while run-time errors (violated constraints, duplicate primary keys, etc.) don't get raised.

To get all errors, use dbFailOnError. This will also cause the entire query to fail if a single operation encounters an error (e.g. one row violates a constraint), while without it only the failed operation won't go through.

So, in summary, use this:

Currentdb.Execute SQL, dbFailOnError
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

dbFailonError does not solve this problem for me. One field in my table is set up with a combobox list and it is set to be limited to the list. However, when I run the CurrentDb.Execute code with a value that is not in the list, nothing is added to the table and no error is returned to my On Error code. I'm using Access 2016. It works fine with an incorrect field header, but not with an incorrect value.
@BenW That's a different question. Limit to list is an UI constraint, not a database constraint, thus doesn't trigger dbFailOnError. If you ask that here and provide an minimal reproducible example, I can probably provide a solution.
The code is exactly like the code that P.Weg shared, except that the ProjectID field is a text field and is limited to a list (list supplied by a linked table). I don't understand why this is not a database constraint when it causes my SQL insert statement to fail. But the main question is why it doesn't trigger any errors.
@BenW I don't understand why this is not a database constraint when it causes my SQL insert statement to fail. That's exactly the point. It usually doesn't cause an insert to fail, unless there's something else in the mix, such as a relation between the table with referential integrity. That's why I want the minimal reproducible example and will not answer this here.
Erik, you were right that this is caused by a table relation with referential integrity (not a linked field list), but I still don't understand why it doesn't throw an error. I have created a separate question here with a link to my minimal database. stackoverflow.com/questions/64216792/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.