CurrentDb.Execute vs. DoCmd.RunSQL

[cinnie]

hi to all

I was interested in answers given to a posting a day or two ago on how to
store a value, for later use, in an unbound table.

Say we have mySQL = "UPDATE tableName SET tableName.[fieldName] ...." etc

The responders suggested using code like...
CurrentDb.Execute mySQL, dbFailOnError

I tried this in my application and it works great, but I have two questions.

a) I had been doing the same thing with code like...
DoCmd.RunSQL mySQL
Is there any difference? Does one method have any advantages over the
other?

b) When using CurrentDb.Execute mySQL, dbFailOnError, I get a "variable not
defined" compile error. (it doesn't like the 'dbFailOnError'). I can get
rid of this error by removing 'Option Explicit' from the module's header, but
I'm not sure why I should have to do this.

Thanks in advance for any comments

 

[Rick Brandt]

hi to all

I was interested in answers given to a posting a day or two ago on
how to store a value, for later use, in an unbound table.

Say we have mySQL = "UPDATE tableName SET tableName.[fieldName]
...." etc

The responders suggested using code like...
CurrentDb.Execute mySQL, dbFailOnError

I tried this in my application and it works great, but I have two
questions.

a) I had been doing the same thing with code like...
DoCmd.RunSQL mySQL
Is there any difference? Does one method have any advantages
over the other?

b) When using CurrentDb.Execute mySQL, dbFailOnError, I get a
"variable not defined" compile error. (it doesn't like the
'dbFailOnError'). I can get rid of this error by removing 'Option
Explicit' from the module's header, but I'm not sure why I should
have to do this.

Thanks in advance for any comments

It is related to the dual nature of using Access. There is the Jet database
engine which can be interacted with from code using DAO or ADO object
libraries and then there is the "Access" interface which can also be
manipulated from code.

The execute method is a DAO method of the database object and it interacts
directly with the Jet engine. Anything that starts with "DoCmd" is part of
the Access interface.

Generally, the Execute method is better. No "are you sure" prompts and you
can trap for errors. On the other hand RunSQL can invoke the expression
service so it can resolve things like "Forms!NameOfForm!NameOfControl" where
the Execute method cannot. That limitation of Execute is easily worked
around though by taking the reference out of the SQL quoted string so VBA
can evaluate it...

MySQL = "SELECT * FROM MyTable WHERE SomeField = '" &
Forms!NameOfForm!NameOfControl & "'"

I don't know why you are getting a variable error unless you have a missing
reference. dbFailOnError should be recognized.

 

[Albert D. Kallal]

b) When using CurrentDb.Execute mySQL, dbFailOnError, I get a "variable
not
defined" compile error. (it doesn't like the 'dbFailOnError'). I can get
rid of this error by removing 'Option Explicit' from the module's header,
but
I'm not sure why I should have to do this.

Using the option explicit means that you must declare and define all
variables that you use. if you leave out option explicit, then you can type
any old name, at any point at anytime anywhere in your code and the variable
will be created on the fly.

it is far better to reuse always option explicit for all of your application
development, since therefore a simple missed I ping of a variable name who
will be instantly caught and recognized by the compiling process.

in fact under tools options (while in the code editor), the first thing I do
when after installing MS access on a new computer is set the option called
require variable declaration.

To fix your error, you must go:


dim mySQL as string

you need to declare the above air ball in the code protein that uses the
variable mySQL.

so as a general coding and practice for writing more reliable and better
code, it is better to use option explicit, and therefore require you to a
always declare all of your variables you use in code.

 

[AccessVandal via AccessMonster.com]

hi to all

I was interested in answers given to a posting a day or two ago on how to
store a value, for later use, in an unbound table.

Never heard of "unbound table". Is there something I missed?

b) When using CurrentDb.Execute mySQL, dbFailOnError, I get a "variable not
defined" compile error. (it doesn't like the 'dbFailOnError'). I can get
rid of this error by removing 'Option Explicit' from the module's header, but
I'm not sure why I should have to do this.

You don't need to remove "Option Explicit". It's very likely that you have
not set the Object References for DAO or ADO object library in the VB Editor.

The advantage of using Docmd.RunSQL is that you don't need this object
librarys.

To use the Excute method you'll need these object librarys. You'll need to
declare the object like....

For ADO
Dim Con As ADODB.Connection
Dim Rst As ADODB.RecordSet

For DAO
Dim db As DAO.DataBase
Dim rst As DAO.RecordSet

 

[Evi]

I'm guessing that an unbound table one whose only purpose is to store a
value, often in a single record, and is usually accessed by code.
Perhaps a visible equivelent of using Write in your code.

I've found that getting the "You have chosen to delete/append" message can
be worrying for some users even though everything is working exactly as it
should. If the code is something that is working in the background (eg a
necessary event that happens automatically when a form is closed) then
Execute saves unnecessary panic. If the user is pressing a button to make
something happen then RunSql provides the user with an extra chance to say
'Sorry, my finger twitched'.


Evi