locked
Error Message Using DB.Execute on SQL Server - Must use dbSeeChanges RRS feed

  • Question

  • Uh, found a post indicating that dbFailOnError and dbSeeChanges are enumerated and that to designate them both, just add them together, but its not working and I'm still getting the ever popular 'You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.'.

    Public Function deleteTrailerActivityHeaderAndChildren(lngTrailerActivityHeaderId As Long)

    On Error GoTo Err_deleteTrailerActivityHeaderAndChildren

        Dim wrksp As DAO.Workspace
        Dim db As DAO.Database
        Dim strDeleteChild As String
        Dim strDeleteParent As String
        Dim strComment As String
        Dim errLoop As Error
       
        strDeleteChild = "DELETE * FROM tblTrailerUtilizationDetails WHERE lngTrailerActivityHeaderId = " & lngTrailerActivityHeaderId
        strDeleteParent = "DELETE * FROM tblTrailerActivityHeaders WHERE lngTrailerActivityHeader = " & lngTrailerActivityHeaderId
       
        Set wrksp = DBEngine.Workspaces(0)
        Set db = CurrentDb
       
        wrksp.BeginTrans
       
        On Error GoTo Err_dbExecute
            strComment = "Delete child records"
            db.Execute strDeleteChild, dbFailOnError + dbSeeChanges
            If DBEngine.Errors.Count = 0 Then
                strComment = "Delete parent"
                db.Execute strDeleteParent, dbFailOnError + dbSeeChanges
                    If DBEngine.Errors.Count = 0 Then
                        strComment = "Commit changes"
                        wrksp.CommitTrans
                    Else
                        strComment = "Rollback changes to tblTrailerActivityHeaders"
                        wrksp.Rollback
                        MsgBox "Record cannot be deleted. Unable to delete the Trailer Utilization Detail records.", vbCritical
                    End If
            Else
                strComment = "Rollback changes to tblTrailerUtilizationDetails"
                wrksp.Rollback
                MsgBox "Record cannot be deleted. Unable to delete the Trailer Utilization Detail records.", vbCritical
            End If
           
        Debug.Print DBEngine.Errors(0).Description
           
        On Error GoTo Err_deleteTrailerActivityHeaderAndChildren
           
        Set db = Nothing
        Set wrksp = Nothing
       
    Exit_deleteTrailerActivityHeaderAndChildren:
        Exit Function

    Err_deleteTrailerActivityHeaderAndChildren:
        MsgBox getDefaultErrorMessage("Module - Manage TrailerUtilization Details", "deleteTrailerActivityHeaderAndChildren", Err.Number, Err.Description), vbCritical
        Resume Exit_deleteTrailerActivityHeaderAndChildren
       
    Err_dbExecute:
        If DBEngine.Errors.Count > 0 Then
            For Each errLoop In DBEngine.Errors
                Debug.Print getDefaultErrorMessage("Module - ManageTrailerUtilizationDetails", "deleteTrailerActivityHeaderAndChildren.Err_dbExecute", errLoop.Number, , "(" & strComment & ") " & errLoop.Description)
            Next errLoop
        End If
        Resume Next
       
    End Function


    David H
    Friday, July 9, 2010 8:41 PM

All replies

  • Your code looks OK. The error message is unreasonable since you're not even using recordsets. Did you try it without dbSeeChanges?

    Do you have a Primary Key in both tables, and have the linked tables been refreshed?

    Did you step through in the debugger to verify the expected code path actually happens?


    -Tom. Microsoft Access MVP
    Sunday, July 11, 2010 2:58 PM
  • 1) There error message is that I need to use dbSeeChanges. So even though its there its not being recognized.

    2) Yes and yes.

    3) I haven't gotten to the point of ensuring that the If...then works properly as the first delete keeps crapping out. My plot is to deliberately cause errors to ensure that it works on all paths.


    David H
    Sunday, July 11, 2010 10:34 PM
  • Odd.

    I changed the .Execute statement to

    db.Execute strDeleteChild, dbSeeChanges

    However, the code is returning the ever popular 'Too few parameters. Expected 1'. When I explicity Debug.Print the SQL statement and then copy & paste it into a new query, it successfully executes.


    David H
    Monday, July 12, 2010 1:52 PM
  • Uh, found a post indicating that dbFailOnError and dbSeeChanges are enumerated and that to designate them both, just add them together, but its not working and I'm still getting the ever popular 'You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.'.

    Public Function deleteTrailerActivityHeaderAndChildren(lngTrailerActivityHeaderId As Long)

    On Error GoTo Err_deleteTrailerActivityHeaderAndChildren

        Dim wrksp As DAO.Workspace
        Dim db As DAO.Database
        Dim strDeleteChild As String
        Dim strDeleteParent As String
        Dim strComment As String
        Dim errLoop As Error
       
        strDeleteChild = "DELETE * FROM tblTrailerUtilizationDetails WHERE lngTrailerActivityHeaderId = " & lngTrailerActivityHeaderId
        strDeleteParent = "DELETE * FROM tblTrailerActivityHeaders WHERE lngTrailerActivityHeader = " & lngTrailerActivityHeaderId
       
        Set wrksp = DBEngine.Workspaces(0)
        Set db = CurrentDb
       
        wrksp.BeginTrans
        ...<snip the rest>...
       


    David H


    Maybe nothing, but I'm not sure about using CurrentDb with an explicit workspace.  See if this makes any difference:

    Set db = wrksp.OpenDatabase(CurrentDb.Name)

    May not make a difference, but at least you'll know you're dealing with the correct database and workspace.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Monday, July 12, 2010 2:50 PM
  • I had to move on with my life and just turned on Cascade Deletes, although I personally abhor them like a Democratic Congress being in session.

    If I weren't working against a deadline, I'd try going the route of explicity setting the DB object using the DBEngine. I did notice that the SQL statement that I was using worked when I copied and pasted it as a new query where it ran fine. When I tried to run in SQL Server Management Studio it returned an error which led me to suspect that there might be a difference between the two.

    Generally, I wanted to delete child records and if it succeed delete the parent.

    For some reason, I've got the belief that you can only use .Execute within a transaction, but now I'm thinking that that may not be true. If the .Delete method of a DAO recordset is executed within a transaction, can't it also be rolled back?

    I really, really need to stop trying too many things all at once. I'm starting to confuse things.


    David H
    Monday, July 12, 2010 4:56 PM
  • There is a standard way to debug the "Too few parameters" error:

    dim qd as dao.querydefs

    set qd = currentdb.querydefs("myQuery")

    dim par as dao.parameter

    for each par in qd.parameters

      debug.print par.name

    next

    Often the culprit will be an object reference that cannot be evaluated e.g. select * from mytable where myfield=forms!myform!mycontrol

    You can deal with this by writing:

    qd.parameters(i).value = eval(qd.parameters(i).name)

     


    -Tom. Microsoft Access MVP
    Tuesday, July 13, 2010 4:36 AM
  • This problem didn't go away yet. I'm having it too in Access 2007 (SP2 not installed yet) with a SQL Server 2008 Express backend. I'm sure my code is not reference any form controls or other object references. Access just complains if you try to run an Execute with "DELETE" on a table that has an identity column. I've resorted to deleting using a DAO recordset and loop instead for now.

    I actually started out using MySQL with MySQL's ODBC drivers and my statement worked fine. Only when I migrated to SQL Server did I start to have this problem.

    Just sayin...

    Wednesday, January 26, 2011 8:01 AM
  • Hi aychekay
    Try following:
    - Add a TimeStamp Datatype Column to the table on the SQL Server
    - Rebind the tables into your database
    - Try your code again
     
    You never should run an Access Application against an SQL Server in readwrite mode without having TimeStamp datatype columns in your tables. Already for performance reasons this is a must and also, if you have datetime datatype fields on the SQL Server that are not exclusively maintained by Access, as the SQL Server may add miliseconds when writing the data. Same for Single precision datatypes, that may be rounded until you get the result back from the server. If you have a Timestamp datatype column in your table then Access is sure the record wasn't changed in the meantime and will not recheck the received record again against the database for changes in one of it's fields.
     
    Henry
     

    This problem didn't go away yet. I'm having it too in Access 2007 (SP2 not installed yet) with a SQL Server 2008 Express backend. I'm sure my code is not reference any form controls or other object references. Access just complains if you try to run an Execute with "DELETE" on a table that has an identity column. I've resorted to deleting using a DAO recordset and loop instead for now.

    I actually started out using MySQL with MySQL's ODBC drivers and my statement worked fine. Only when I migrated to SQL Server did I start to have this problem.

    Just sayin...

    Thursday, January 27, 2011 11:30 AM
  • Henry,

    It didnt' work.

    I added the time stamp column to my table named "tag". Next I reconnected the linked tables using my VBA routine. This allowed me to see the additional field when I went into the design view for the said table. Next I tried my code again and it bombed out on my delete statement with the same error message.

    Thursday, January 27, 2011 5:53 PM
  • Long shot: do you have a trigger on that table? Or cascading relationships?
    -Tom. Microsoft Access MVP
    Thursday, February 3, 2011 10:33 PM
  • no triggers or relationships.
    Thursday, February 3, 2011 10:39 PM
  • Did you run DBCC CHECKDB to rule out database corruption?
    -Tom. Microsoft Access MVP
    Friday, February 4, 2011 1:55 AM
  • Yes I just tried that and it ran without any error messages and I'm still getting the same error message.

    Here's my SQL DELETE statement:

    CurrentDb.Execute ("DELETE * FROM tblAudit WHERE dteDate < #01/01/2009#"), dbFailOnError

    The error message returned is:

    Run-time error '3622':

    You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

     

    We are using DSN-less ODBC linked tables together which are using the SQL Server 10.x Client Driver.

    Friday, February 4, 2011 4:12 AM
  • and why don't you just add the option at the end? 
    + dbSeeChanges
    
    Friday, February 4, 2011 5:43 AM
  • I guess I got sidetracked by the OP saying that adding them together did not work for him. I added them together and it does appear to resolve the problem. As per Henry's suggestion, my new working statement looks like this:

    CurrentDb.Execute ("DELETE * FROM tblAudit WHERE dteDate < #01/01/2009#"), dbFailOnError + dbSeeChanges

    Thanks for the help.

    Friday, February 4, 2011 1:35 PM
  • Hi Aychekay
     
    There it seems to be another problem. As soon as you have SQL Server tables involved always add the two options as mentioned below.
     
    BTW: + is actually not completely right. Better would be
    dbFailOnError Or dbSeeChanges
    + works, too, but does something different. It adds the values of the two options while OR would do what should be done: a binary OR between two numbers (what results in a plus).
    The difference is following:
    "dbFailOnError + dbSeeChanges + dbSeeChanges" add's dbSeeChanges twice and therefore another option may be activated instead of dbSeeChanges
    "dbFailOnError Or dbSeeChanges Or dbSeeChanges" binary "ors" dbSeeChanges twice what will not change the result at all.
     
    Henry

    I guess I got sidetracked by the OP saying that adding them together did not work for him. I added them together and it does appear to resolve the problem. As per Henry's suggestion, my new working statement looks like this:

    CurrentDb.Execute ("DELETE * FROM tblAudit WHERE dteDate < #01/01/2009#"), dbFailOnError + dbSeeChanges

    Thanks for the help.

    Monday, February 7, 2011 7:39 AM
  • GOT IT!!

    This problem was making me tear my hair out!

    All you have to do is this:

    Dim db As Database
    Dim Rst As Recordset
    
    Set db = CurrentDb
    Set Rst = db.OpenRecordset("SELECT * FROM YourTable;", dbOpenDynaset, dbFailOnError + dbSeeChanges)
    

    dbopendynaset is the key!

    • Proposed as answer by busyfritz Friday, January 30, 2015 3:08 AM
    Friday, January 30, 2015 3:07 AM