Error 91 with Execute, Works Fine with Docmd.RunSQL?

[CPGDeveloper]

Hello All,

I'm using Allen Brown's Code as a Template to create an audit trail in my db (MS Access Front End, Azure SQL Server Back End). Part of it is a function that executes queries that are called in the BeforeUpdate Event of the primary data entry form. The function starts as follows:

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, ngKeyValue As Long, bWasNewRecord As Boolean) As Boolean

On Error GoTo Err_AuditEditBegin

Dim db As DAO.Database ' Current database
Dim sSQL As String

'Remove any cancelled update still in the tmp table.
Set db = DAO.CurrentDb
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbSeeChanges

...
End Function

As I mention, I call this function from the primary form's BeforeUpdate Event, as follows:

bWasNewRecord = Me.NewRecord
PUBID = Me.InvID
Call AuditEditBegin ("tblInv", "audtmpInv", "InvID", PUBID, bWasNewRecord)

The issue I'm having is the db.Execute line -- I get Error '91' -- Object or With Variable Not Set. I can, however, get the SQL Statement to Execute using DoCmd.RunSQL.

When I step through the code all the variables are properly defined, and are cleanly passed through to the function. It works fine using DoCmd.RunSQL, however my understanding is that Execute is faster, and would like to understand why it is not working in this instance. I do have 'Execute' working in very similar setups throughout my project, but here somehow it is failing. Any thoughts would be greatly appreciated. Thank You.

 

[welshgasman]

I have always used Delete *
I have also always used Set db=CurrentDB() ?

 

[jimrward]

Does your code have a label called Err_AuditEditBegin for your error handler

 

[xenou]

Is this table you are deleting from in the local access database or on the azure back end database?

Also set DB = DAO.CurrentDb looks weird to me. I have always seen set DB = CurrentDb I guess you can look into whether you are doing this right. That certainly fits the error.

Also have you tried other options for the second argument? (Or no second argument, which I think is also possible).

 

[CPGDeveloper]

Hi guys -- thanks for your replies...my responses

• I mis-transposed the code a bit, I do in fact use 'Set db = CurrentDb'
• I do have an error handler called 'Err_AuditEditBegin'
• I am deleting a table in the Azure SQL Back End
• When working with tables in a SQL Back End, I find that it does not work without the 'dbSeeChanges' argument, as I've tried other variations with no success in other sections of my project

Magically, my code seems to be working fine today, so it's a bit of a mystery -- I'm guessing there was some issue with my connection and it couldn't find the table I was trying to delete. Either way -- thank for your help.