DBEngine - Access 2003 vs Access 2010

[Alan Yim]

Hi folks,

My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question used work in 2003 (see below code).

Private Sub engSave_Click()

    Dim strSql3 As String

    'Archive order number and cost data for engine.
        strSql3 = "UPDATE tblQuotePkgEngine INNER JOIN qryQuotePkgEngine ON " & _
            "(tblQuotePkgEngine.engPrcID = qryQuotePkgEngine.engPrcID) " & _
            " AND (tblQuotePkgEngine.pkgID = qryQuotePkgEngine.pkgID) " & _
            " SET tblQuotePkgEngine.soHist = [qryQuotePkgEngine]![OrderNo], " & _
            " tblQuotePkgEngine.costHist = [qryQuotePkgEngine]![Cost (CDN)], " & _
            " tblQuotePkgEngine.prcHist = [qryQuotePkgEngine]![Price] " & _
            " WHERE (((tblQuotePkgEngine.pkgID)= " & Me.pkgID & "));"
        DBEngine(0)(0).Execute strSql3, dbFailOnError

        Forms![fmQuotePkgDetails].Refresh
        Forms![fmQuotePkgDetails]![modDate] = date
       
        MsgBox ("Engine modification now saved.")
    
End Sub

Since the upgrade to 2010, this chunk of code no longer seems to work. No error messages are given. In fact by all appearances, it looks like it executes the code as the final message I see is the msgbox item but when you look on the table, values that are supposed to be copied to the archive fields are not being executed. My suspicion is that it has to do with the "DBEngine(0)(0) .Execute strSql3, dbFailOnError" chunk of code, in particular the DBEngine(0)(0) piece.

I'm assuming Access 2010 does not recognize this anymore?

Can someone enlighten me please as this is something I've been trying to chase down for the last day or so.

Thanks in advance.

Alan

[NeoPa]

I can't help much here Alan I'm afraid, but were I in your position I would :

1. Trace (See Debugging in VBA) through the code until you determined that the results were not as you expected. That will give you which line is not behaving as expected.
2. Search the web for pages dealing with differences between versions of Access (You may want to start at Exploring Office 2010).