Unanswered: qd.Execute isn't giving me an error when it should
I'm enhancing someone else's code which is always fun . Actually, there was an existing form similar to the new one I had to develop which cut my development time in half. It's an Access 2003 project with VBA code and Access tables linked to SQL Server as the backend db.
I was executing the following code with apparently no problem, but when I checked my table no new row had been added. When I ran the same query to add the row in SQL Server natively I got an error. I think this is very bad and I don't understand it. Why is Access not reporting back an error that occurs when an INSERT is requested? Here is my code and as much information as I think you'll need, if you care to help (which I hope you do!)
Private Sub btnAdd_Click()
Dim qd As QueryDef
Dim rs As Recordset
On Error GoTo Error_btnAdd_Click
Set db = CurrentDb()
I get a MsgBox which tells me the new quote has been added, but alas it is not so. When I run "AddHistoryDetails aqry" in SQL Server Query Analyzer, it tells me this:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'HistoryDetails' with unique index 'Secondary'.
The statement has been terminated.
I'd like to know this in my VB app! Do I have to check the results of qd.Execute() (or maybe I should ask, Can I?) If not, how else can this error be known? Shouldn't ON ERROR GOTO catch it? I'd hate to finish this project and have the code tell the end user in a production environment that data was inserted when in fact it wasn't (clearly the developer before me didn't care, since that part of the code is unchanged from him/her).
The first hit I got doing a Google search told me this:
Saved queries become part of what is called the Querydef Collection in Microsoft Access and you can manipulate them by setting an object reference. It only takes a few lines of code and is very powerful:
Dim qdf As DAO.Querydef
Set qdf = CurrentDB.Querydefs("qGeneric")
qdf.SQL = "SELECT * INTO titles_bkup
This method suppresses the typical Access warning messages, such as "You're about to run a query that will ..." and the ones you get if there are any records that fail, along with a vague reason for the failure. However, you will need to handle VBA errors, such as the one shown below.
Is that right? It suppresses messages for records that fail? Can I trap this in my code so I can handle it?
I will research FailOnError but this is what I just read:
You can use the FailOnError property to specify whether an update or delete query that is run against an ODBC data source will terminate if an error occurs. The FailOnError property lets you optimize bulk update queries run against ODBC data sources so that they are executed on the server if the partial-failure behavior of the Microsoft Jet database engine isn't desired.
Note The FailOnError property applies only to update and delete queries.
May I ask why,when I run the query in my VBA code, I get "ODBC--call failed." but when I run it in Query Analyzer I get "Cannot insert duplicate key row in object 'AddHistoryDetails' with unique index 'Secondary'.
The statement has been terminated." Obviously the latter is much more helpful.