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

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
155
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880
I have always used Delete *
I have also always used Set db=CurrentDB() ?
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Does your code have a label called Err_AuditEditBegin for your error handler
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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).
 
Last edited:

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
155
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,339
Messages
5,624,101
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top