Declare sql or not?

[J-P-W]

Hi,

Should I:

--------------------
Dim sqlA As String
sqlAll = "DELETE * FROM tblName;" '....or whatever!
DoCmd.RunSQL sqlA
--------------------

Or is it acceptable to just:
--------------------
DoCmd.RunSQL (DELETE * FROM tblName;)
--------------------

I know both work, but is there a reason I should use one over the
other?

Thanks, Jon

[Bob Quintal]

"J-P-W" <jo******@gmail.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:

Hi,

Should I:

--------------------
Dim sqlA As String
sqlAll = "DELETE * FROM tblName;" '....or whatever!
DoCmd.RunSQL sqlA
--------------------

Or is it acceptable to just:
--------------------
DoCmd.RunSQL (DELETE * FROM tblName;)
--------------------

I know both work, but is there a reason I should use one over

the

other?

Thanks, Jon

That depends on the length of the SQL statement. I put short
commands directly into the runSQL().

However, I have code where the sql statement runs to 10,000
characters. By building it in increments it's a lot easier to
debug. I've even resorted to split a statement into sections
Dim sqlSelect as string, sqlFrom as string,
Dim sqlWhere as string, sqlORder as string

then doing
DoCmd.RunSQL(sqlSelect & " " & sqlFrom & " " & sqlWhere)

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Allen Browne]

As long as you add the quotes, you can use:
DoCmd.RunSQL "DELETE FROM tblName;"

For such as simple query, there is not really a point to declaring a string.
For a more complex query statement where you are concatenating values into
the string, declaring a string aids:
a) readability: you can see what's going on, and
b) debugging: you can:
Debug.Print strA

A more important issue is whether the query ran to completion or not. The
Execute method gives you much more flexibilty that RunSQL. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"J-P-W" <jo******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

>
Should I:

--------------------
Dim sqlA As String
sqlAll = "DELETE * FROM tblName;" '....or whatever!
DoCmd.RunSQL sqlA
--------------------

Or is it acceptable to just:
--------------------
DoCmd.RunSQL (DELETE * FROM tblName;)
--------------------

I know both work, but is there a reason I should use one over the
other?

[J-P-W]

Allen Browne wrote:

As long as you add the quotes, you can use:
DoCmd.RunSQL "DELETE FROM tblName;"

For such as simple query, there is not really a point to declaring a string.
For a more complex query statement where you are concatenating values into
the string, declaring a string aids:
a) readability: you can see what's going on, and
b) debugging: you can:
Debug.Print strA

A more important issue is whether the query ran to completion or not. The
Execute method gives you much more flexibilty that RunSQL. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"J-P-W" <jo******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

Should I:

--------------------
Dim sqlA As String
sqlAll = "DELETE * FROM tblName;" '....or whatever!
DoCmd.RunSQL sqlA
--------------------

Or is it acceptable to just:
--------------------
DoCmd.RunSQL (DELETE * FROM tblName;)
--------------------

I know both work, but is there a reason I should use one over the
other?

Thank you, So what I've been doing is fine then...using which ever
methode seemed best at the time!!

Regards

Jon