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]

Re: Declare sql or not?

"J-P-W" <jonpwebb@gmail .comwrote in
news:1156681739 .643743.138000@ i42g2000cwa.goo glegroups.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(sq lSelect & " " & 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]

Re: Declare sql or not?

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" <jonpwebb@gmail .comwrote in message
news:1156681739 .643743.138000@ i42g2000cwa.goo glegroups.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]

Re: Declare sql or not?


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" <jonpwebb@gmail .comwrote in message
news:1156681739 .643743.138000@ i42g2000cwa.goo glegroups.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