Naming and Using Variables

[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

 

[Jeff Boyce]

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

 

[Guest]

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

 

[DS]

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

 

[Marshall Barton]

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?

[snip]

You can reuse the same variable.

 

[John W. Vinson]

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?

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

John W. Vinson [MVP]

 

[Guest]

Yes. With any Action Query

 

[DS]

Thanks Jeff,
That makes sense!
DS

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

 

[DS]

Correct
DS

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

 

[DS]

Great! I like that!
DS

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

 

[DS]

Thank you Marsh
DS

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?

[snip]

You can reuse the same variable.

 

[DS]

Thank you John.
DS

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

John W. Vinson [MVP]

 

[DS]

So can I do this with DELETE as well.

CurrentDb.Execute "DELETE * FROM tblTempChecks",dbFailOnError

Thanks
DS

 

[DS]

THANKS
ds

Yes. With any Action Query