VB Delete Opinion

[Chefjay22]

Hey guys. I posted earlier on another issue and got a great response... so I
thought I would get your opinion on something.

First, I am not a programmer by trade... I am a MS Access Expert minus the
programming side. I am normally able to get Access to do almost anything I
need without, or with very limited programming. So here is the situation.

I have a procedure that runs a process. In this process I have about 8
queries that manipulate data in my tables. Two of my queries delete all
records from temp tables. Here is my function:

Private Sub CatchMissingAccounts()

On Error GoTo Err_cmdCatchMissingAccounts_ReportError

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteAccounts4MissingCompare"
DoCmd.OpenQuery "qryDeleteNewAccountsMissingAccts"
DoCmd.OpenQuery "qryBuildMPAccountsTemp" 'Writes MP Accounts to
tblAccounts4MissingCompare
DoCmd.OpenQuery "qryBuildSamAccountsTemp" 'Writes SAM Accounts to
tblAccounts4MissingCompare
DoCmd.OpenQuery "qryFindMissingAccounts" 'Writes unmatched records to
tblNewAccountsMissingAccounts
DoCmd.OpenQuery "qryUpdateRecoveredMissingRecord" 'Update MissingRecord Flag
DoCmd.OpenQuery "qryWriteMissingRecords2NewAccts" 'Writes completed records
to tblNewAccounts
DoCmd.SetWarnings True

Exit_CatchMissingAccounts:
Exit Sub

Err_cmdCatchMissingAccounts_ReportError:

MsgBox Err.Description
Resume Exit_CatchMissingAccounts

End Sub


My question is this. On the two delete queries,
"qryDeleteAccounts4MissingCompare" & "qryDeleteNewAccountsMissingAccts" my
boss wanted me to use VB code, And I assume SQL to delete the record sets...
what a pain in the butt! Both of the queries are SQL queries and they delete
the records from the tables fine... but in an effort to make my life a living
Hot place she wants me to create a recordset, open it...delete it, and close
it...

So is my above method better than this? Personally I feel my one line of
code is more efficient... but I need to be able to prove to her that her
method is a waste of time, and not to mention not in better practice. My
impression is that it would be best to go directly to the recodset to delete
the records than to create a recordset, only to delete it... What do you guys
think?

Thanks!

 

[Robert Morley]

You are absolutely right, it WOULD be a waste of time. Opening a recordset
and deleting each record one-by-one will ALWAYS take longer than running an
action query. The question is: does your boss have some other reason to
open it as a recordset?

About the only way you could make your code below any faster (and if it is,
it would probably be only very slightly) would, amusingly enough, be nearly
the same solution I suggested earlier (see code, below). It's been a while
since I've worked with DAO significantly, but IIRC, with this method there's
no need for the SetWarnings False; the code runs without asking any
questions. (Note: I put your comments on separate lines, just so the
wrapping wouldn't confuse anything.)

Private Sub CatchMissingAccounts()
Dim db As DAO.Database

On Error GoTo Err_cmdCatchMissingAccounts_ReportError

Set db = CurrentDb()
With db
.Execute "qryDeleteAccounts4MissingCompare"
.Execute "qryDeleteAccounts4MissingCompare"
.Execute "qryDeleteNewAccountsMissingAccts"
.Execute "qryBuildMPAccountsTemp"
'Writes MP Accounts to tblAccounts4MissingCompare
.Execute "qryBuildSamAccountsTemp"
'Writes SAM Accounts to tblAccounts4MissingCompare
.Execute "qryFindMissingAccounts"
'Writes unmatched records to tblNewAccountsMissingAccounts
.Execute "qryUpdateRecoveredMissingRecord"
'Update MissingRecord Flag
.Execute "qryWriteMissingRecords2NewAccts"
'Writes completed records to tblNewAccounts
End With

Exit_CatchMissingAccounts:
Exit Sub

Err_cmdCatchMissingAccounts_ReportError:

MsgBox Err.Description
Resume Exit_CatchMissingAccounts

End Sub

Having said all of the above, it wouldn't be a bad idea to try doing some of
these as recordset updates instead, just to you can get a feel for the
strengths of doing it that way. In particular, it's useful to check if a
record exists before changing it/deleting it/whatever, and to merge what
might be multiple operations if done via queries into a single operation.


Rob

 

[Klatuu]

I would also recommend the Execute method and using the with contstruct will
improve performance even more; however, when you use the Execute method, it
is important to use the dbFailOnError option. Because the Execute method
bypasses the Access User Interface (that is what makes it faster), you will
not see an error if the action fails. The dbFailOnError will cause it to
error correctly:

.Execute "qryDeleteAccounts4MissingCompare", dbFailOnError

And yes, using recordset processing is much slower. There is absolutely no
justifyable reason to do it this way.

 

[Robert Morley]

Thanks for the reminder, Dave, you're absolutely right. It's been a while
since I've done anything in DAO. <blush>


Rob

 

[Klatuu]

No Problem, Robert. Just wanted to make sure the poor OP knew about it.

I pity a developer who has to endure inane requirements like that. It it
like hiring a carpenter and demanding he use only rubber hammers.

 

[George Nicholson]

Did your boss actually say "Use code to Delete the records" or "Use code to
open a recordset and delete the records"? Two entirely different things. I
would have no argument with the first request since its generally what I do
anyway.

Giving your boss the benefit of the doubt for just a second, maybe she
simply doesn't like to rely on a named query to delete records.

Or would simply be more comfortable if no Delete queries exist in the db at
all (because if they get run accidentally....).

You can argue that Append and Update queries are just as likely to create
havok as Delete queries, but different people have different sensibilities.

Now, opening a recordset is simply a very bad substitute, but the following
is not:

strSQL = "DELETE * FROM tblAccounts4MissingCompare"
CurrentDB.Execute strSQL, dbFailOnError
Debug.Print CurrentDB.RecordsAffected & " records deleted from
tblAccounts4MissingCompare"

strSQL = "DELETE * FROM tblNewAccountsMissingAccounts"
CurrentDB.Execute strSQL, dbFailOnError
Debug.Print CurrentDB.RecordsAffected & " records deleted from
tblNewAccountsMissingAccounts"

Does this serve the same purpose as the saved queries? Yes, but you no
longer need to have the queries sitting out there waiting for someone to run
them accidentally. Even if no one other than Admin or Developers (i.e., your
boss?) can run queries independently, it still eliminates the possibility of
that particular accident from occuring.