Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: qd.Execute isn't giving me an error when it should

    Hi,
    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()

    Me!InquirySK = Me!txtAttachToInquirySK
    Me!BearingSendAs = Me!txtBearingSendAs
    Me!Qty = Me!txtQty
    Me!Price = Me!txtPrice
    Me!SerialNumber = Me!txtSerialNumber
    Me!InquiryDate = Me!txtInquiryDate

    RunCommand acCmdSaveRecord

    If Not IsNull(Me![Price]) Then
    Set db = CurrentDb()
    Set qd = db.QueryDefs("AddHistoryDetails aqry")
    qd.Execute (dbSeeChanges)

    MsgBox ("New quote added")
    Else
    MsgBox ("You must enter a price to send this information to history.")
    Exit Sub
    End If
    Exit_btnAdd_Click:
    Exit Sub

    Error_btnAdd_Click:
    MsgBox Error, 16, "Error " & Err & " - btnAdd_Click"
    Resume Exit_btnAdd_Click

    End Sub


    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).

    Thanks,
    Marlene

  2. #2
    Join Date
    Jul 2006
    Posts
    111
    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
    FROM titles;"
    qdf.Execute

    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?

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The Execute method has an optional argument you may be interested in:

    dbFailOnError
    Paul

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    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.
    <<

    I am doing an Insert.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I have used it on insert queries; it should pop an error message.
    Paul

  6. #6
    Join Date
    Jul 2006
    Posts
    111
    Okay, that's much better.

    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.

    Thanks!....almost resolved!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •