CurrentDb.Execute vs. docmd.runsql

[cisy]

I am wondering what exactly is the difference between the CurrentDb.Execute and. docmd.runsql in VBA.

Thanks

[HansV]

DoCmd.RunSQL operates within the Access interface, while CurrentDb.Execute operates within the underlying database engine (it is a DAO command).
This has - among other things - the following consequences:

1) Since DoCmd.RunSQL works within the Access interface, it "knows" about forms and controls, so you can refer to controls in the SQL string, e.g. [Forms]![frmMyForm]![txtMyControl].
Referring to controls on forms fail with CurrentDb.Execute since DAO doesn't recognize forms and controls.

2) DoCmd.RunSQL will cause Access to ask you for confirmation ("You are about to update/append/delete 37 records ..."), unless you insert a line

DoCmd.SetWarnings False

before invoking RunSQL, and

DoCmd.SetWarnings True

afterwards. CurrentDb.Execute bypasses the Access interface and executes the SQL without asking.

3) For complex queries, CurrentDb.Execute will be faster because it doesn't have to perform all the checks the Access interface does.

4) If you run CurrentDb.Execute on the data currently loaded in a form, and then try to save a record in the form, you may get a message that another user has modified the record. The "other user" is the database engine.

[cisy]

Hans,

Thanks very the insight. Your explanation is very helpful.

[DaveM]

Hans, I echo cisy's thanks for your concise and informative answer.

My scripts are currently a mix of DoCmd.RunSQL and CurrentDB.Execute calls. I discovered that when using CurrentDB.Execute I could add

Code: Select all

Me.Form.RecordLocks = 2

to my Form_OnOpen event to suppress the message about the "other" user, as you noted. In this particular case, this is not an issue, but I generally like to keep locking as granular as possible. It appears that CurrentDB.Execute is the preferred lean and mean alternative to DoCmd.RunSQL; will a generic construct of

Code: Select all

DoCmd.SetWarnings False
CurrentDB.Execute SQLString, dbFailOnError 
DoCmd.SetWarnings True

suppress that pesky "update by another" message while conferring all the performance and feedback benefits, or is there a simpler way?

Cheers!
Dave

[DaveM]

Sorry, I should have referred to the Form_Load() event rather than Form_OnOpen().

I just tried answering my own question and can no longer generate the "update by another user" message, even after resetting Me.Form.RecordLocks = 0 in the Form_Load() event. Can someone present an example of a situation where this message will appear, and recommendations for how to automatically suppress it?

[HansV]

Welcome to Eileen's Lounge!

The error may occur in the following situation:
- You have started editing a record.
- You run code that uses CurrentDb.Execute to modify records in the form's record source, including the current record in the form.
- At this point you don't get an error message yet, so there is no point in trying to suppress errors here.
- When you perform an action that will save the current record, such as moving to another record, you'll get a warning:

x1210.png

- The workaround is to make sure that the current record has been saved before you run the SQL:

Code: Select all

    If Me.Dirty Then
        Me.Dirty = False
    End If
    CurrentDb.Execute SQLString, dbFailOnError

[DaveM]

Brilliant! Many thanks.

Cheers!
Dave