Execute vs RunSQL

[WaiveIt]

For large queries I was getting...

There isn't enough disk space or memory to undo the data changes this action query is about to make​

so I switched some of my...

DoCmd.RunSQL "SQL Statements"

to

CurrentDB.Execute "SQL Statements"

This got rid of the warning message, and works seamlessly with most SQL statements. However, CurrentDB.Execute does not prompt users for a parameter value, like DoCmd does. Using a system parameter value to obtain parameters I encountered...

Runtime Error 3061 Too few parameters, Expected #​

When running SQL statements that get a variable from the user via a system parameter value dialog. You must use a custom control to get any parameters when using Execute.


When I encountered error 3061 I searched the web and didn't find anything related to what was going on in this situation. The error message was actually spot on, but habit takes an error code to the internet. Either way, somebody, someday will search for this. Will text tags(like Italic Code and Indent) get searched in Google?

 

[pbaldy]

Most of us wouldn't use the system parameter prompt anyway, presuming you mean you have bracketed criteria like:

[Enter whatever]

We would either use a form or an input box to gather user input, which gives you more control. To use Execute you'd concatenate those into your SQL:

db.Execute "INSERT....WHERE Field = " & Forms!FormName.ControlName & " rest of SQL"

 

[WaiveIt]

Most of us wouldn't use the system parameter prompt anyway...

Agreed. (certainly why I found no info on that issue)

I settled on the system parameter prompt months ago because I was having syntax problems with the data captured from an input box. I have since learned the way forward, but, none the less, somebody might be using system dialogs on large queries and need this info.

 

[Galaxiom]

DoCmd.RunSQL is a method of the application so it is interpreted by Access, raising parameter dialogs and converting expressions before sending the command to the database engine.

Currentdb.Execute sends straight to the engine so the query must be fully formed as it stands.

BTW Execute should be used with the second argument dbFailOnError so that results such as "can't insert due to ...." will raise an error. Otherwise the user will not get a warning.

 

[pbaldy]

BTW Execute should be used with the second argument dbFailOnError.

I would clarify that to say that the developer should be aware of that argument. There are times when having it fail silently is desirable.

 

[Galaxiom]

I would clarify that to say that the developer should be aware of that argument. There are times when having it fail silently is desirable.

Perhaps, but I can't think of one.

If a particular type of fauilure is expected (eg duplicates inserted into unique index fields) then it is better to actively manage the code (eg use an outer join with Not Null criteria on the indexed field) to avoid them.

The danger of simply ignoring all errors because you expect some is quite dangerous because other unanticipated errors would also be ignored.

 

[pbaldy]

Perhaps there was a better way, but I have an app that uses it. Drivers can use one or more rates during their shift, which leads to a classic one-to-many form/subform setup. For data entry purposes, mgmt wanted to present the user with all the available rates rather than have them create a subform record for each rate used. That means they can just enter down the subform filling in a quantity field for any rate used (the quantity field is the only unlocked field, so they just go down the form).

When they load the form I run an append query that adds records for each rate plus the driver and date fields. When they close the form I delete any records with a zero quantity. The relevant part happens if they go back and edit a driver's data. At that point, a record might already exist for a given rate, so the append query would fail on that rate. Excluding dbFailOnError lets it fail silently, as I want that rate to fail and the others to get added.

 

[Galaxiom]

That is a case where I would use an outer join with the Null test in the query.

The odds of your way going unexpectedly wrong are obviously very slim but as a matter of principle I am quite averse to errors as part of normal procedures.

Similarly I avoid techniques that use On Error Resume, catching expected errors and testing them. I would much rather test for expected exceptions first and leave errors for actual errors.

It is a personal preference.

 

[pbaldy]

I actually have the same preference for coding to handle expected exceptions rather than relying on error traps or whatever to catch them, but didn't see a clean way around it here.