Quick, Easy question.....

[Dan0331]

I've got an SQL statement within a For....Next loop. Should the SQL be executed after each loop, or once after and outside of the loop?


Thanks!

[Bonker Gudd]

It'll be executed for each loop, surely?

[Buzby]

It kinda depends on what it does, and what you want to achieve..?!

[Dan0331]

It's just a simple append statement. Pasting 100 records from one Access table to another. I'm going to try to paste the code below. Does the execute statement come before or after the Next?

code

Dim SQL as String
Dim LanceJet as Database

For X = 1 To 100

SQL = "INSERT INTO SiteInfo ( SiteName, MainURL, Signups )"
SQL = "SELECT [WM Site Info].SiteName, [WM Site Info].MainURL, [WM Site Info].Signups"
SQL = "From [WM Site Info]"
SQL = "ORDER BY [WM Site Info].Signups DESC;"

Next

[FireBeast]

I believe you'll have to do an execute command before the next command.


FireBeast

PS just out of interest, why don't you do an insert sql with a where clause which will pick up all records which match your selected criteria?

[Bonker Gudd]

Can do either.
Would probably be quicker to append them together and run after the Next.

[Dan0331]

I'm getting a syntax error in the SQL code. Can you take a look at it above, and let me know what I've done wrong?

Thanks

[Bonker Gudd]

Looks like you need to join the SQL together, use "& _" at the end of the 1st 3 lines, remove the SQL = of the last 3 lines. Don't forget to put spaces before each closing double quote.

[LG]

Hi, Dan0331.


You didn't supply any values to insert.
The syntax should be:
SQL = "INSERT INTO SiteInfo ( SiteName, MainURL, Signups) Values (SELECT [WM Site Info].SiteName, [WM Site Info].MainURL, [WM Site Info].Signups From [WM Site Info]"

Larisa

[Dan0331]

Hey Bonker,

I'm now getting an error stating that the MicroSoft Jet DB cannot find the input query for the following.

SQL = "ORDER BY [WM Site Info].Signups"

I've also tried it with a space at the end:

SQL = "ORDER BY [WM Site Info].Signups "

This is the last line of the SQL statement so I know I don't need the &_ at the end.

Any suggestions?

Thanks for your help.

[JMuller]

Try this one:

SQL = "INSERT INTO SiteInfo (SiteName, MainURL, Signups) "
SQL = SQL & "SELECT [WM Site Info].SiteName, [WM Site Info].MainURL, [WM Site Info].Signups "
SQL = SQL & "From [WM Site Info] "
SQL = SQL & "ORDER BY [WM Site Info].Signups DESC;"

or

SQL = "INSERT INTO SiteInfo (SiteName, MainURL, Signups) " & _
"SELECT [WM Site Info].SiteName, [WM Site Info].MainURL, [WM Site Info].Signups " & _
"From [WM Site Info] " & _
"ORDER BY [WM Site Info].Signups DESC;"

Rgds.