By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Declare sql or not?

P: n/a
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

Aug 27 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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

Aug 27 '06 #2

P: n/a
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?

Aug 27 '06 #3

P: n/a

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

Aug 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.