CODEWITHSUNDEEP
ms-accessvba
P.Weg
P.Weg

Reputation: 77

Currentdb.Execute (Database.Execute) not working or throwing errors

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.

Upvotes: 3

Views: 9508

Answers (1)

Erik A
Erik A

Reputation: 32682

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

Upvotes: 4

Related Questions