Naming and Using Variables

  • Thread starter Thread starter Jeff Boyce
  • Start date Start date
J

Jeff Boyce

As far as I know, you are not required to use three separate variables.

On the other hand, if something blows up, using three separate variables
might give you a better chance at identifying where...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
By the way, I'll assume your three SQL statements were intended for example
only, since the first and third seem to be duplicates.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
One variable is sufficient.
It is not even necessary to use a variable at all. It could be done with:

CurrentDb.Execute "INSERT INTO tblChecks SELECT * FROM tblTempChecks",
dbFailOnError

Now, in reality, it would be even faster if rather than using SQL in your
code, you used a stored query. The reason is, that before queries are run,
they are optimized with Rushmore. Stored queries have already been
optimized, so the time to compile and optimize the query is saved over using
"in line" code.

The Execute method is the fastest way to execute an action query, so if you
use stored queries and the Excute method, you have (IMHO) the best way to do
it.

CurrentDb.Execute qappSomeQuery, dbFailOnError
 
I have three SQL commands in a row, I was wondering can I use the same
variable name or do I have to use three seperate names?
Thanks
DS

Dim CheckSQL as String
Dim CheckDetailSQL as String
Dim VoidSQL as String

''CheckSQL = "INSERT INTO tblChecks SELECT * FROM tblTempChecks"
''db.Execute CheckSQL, dbFailOnError
''CheckDetailSQL = "INSERT INTO tblHistoryCheckDetails SELECT * FROM
tblTempCheckDetails"
''db.Execute CheckDetailQL, dbFailOnError
"VoidSQL = "INSERT INTO tblChecks SELECT * FROM tblTempChecks"
''db.Execute VoidSQL, dbFailOnError

OR...........

Dim MySQL as String

''MySQL = "INSERT INTO tblChecks SELECT * FROM tblTempChecks"
''db.Execute MySQL, dbFailOnError
''MySQL = "INSERT INTO tblHistoryCheckDetails SELECT * FROM
tblTempCheckDetails"
''db.Execute MySQL, dbFailOnError
''MySQL = "INSERT INTO tblChecks SELECT * FROM tblTempChecks"
''db.Execute MySQL, dbFailOnError
 
DS said:
I have three SQL commands in a row, I was wondering can I use the same
variable name or do I have to use three seperate names?
Click to expand...
[snip]

You can reuse the same variable.
 
I have three SQL commands in a row, I was wondering can I use the same
variable name or do I have to use three seperate names?
Click to expand...

Just use the same name, if you don't need to keep the SQL string for some
other use.

John W. Vinson [MVP]
 
Thanks Jeff,
That makes sense!
DS
Jeff Boyce said:
As far as I know, you are not required to use three separate variables.

On the other hand, if something blows up, using three separate variables
might give you a better chance at identifying where...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Correct
DS
Jeff Boyce said:
By the way, I'll assume your three SQL statements were intended for
example only, since the first and third seem to be duplicates.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Great! I like that!
DS
Klatuu said:
One variable is sufficient.
It is not even necessary to use a variable at all. It could be done with:

CurrentDb.Execute "INSERT INTO tblChecks SELECT * FROM tblTempChecks",
dbFailOnError

Now, in reality, it would be even faster if rather than using SQL in your
code, you used a stored query. The reason is, that before queries are
run,
they are optimized with Rushmore. Stored queries have already been
optimized, so the time to compile and optimize the query is saved over
using
"in line" code.

The Execute method is the fastest way to execute an action query, so if
you
use stored queries and the Excute method, you have (IMHO) the best way to
do
it.

CurrentDb.Execute qappSomeQuery, dbFailOnError
Click to expand...
 
So can I do this with DELETE as well.

CurrentDb.Execute "DELETE * FROM tblTempChecks",dbFailOnError

Thanks
DS
 
Back
Top