SQL in Visual Basic is not working correctly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created in VB some SQL statements in a for next loop.
After approx. 120 for/next loops, Access is not creating the correct result
anymore. There is a counter inside the for/next loop, but the counter is not
correct anymore after 120 (or sometimes more) loops.
Can it be that it is not possible to set a lot of SQL statements one after
the other? Must there be some brake?
See a part of the VB:
I am creating a XML statement with some variables..

For TELLER = 1 To H_TELLER
'Scherm 1
If TELLER = 1 Then
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<screen name=""+'""'+""Screen""+""" & SCREEN1 &
"""+'"" entryscreen=""true"" exitscreen=""false"" transient=""false"">' AS
omschrijving;"
Else
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<screen name=""+'""'+""Screen""+""" & SCREEN1 &
"""+'"" entryscreen=""false"" exitscreen=""false"" transient=""false"">' AS
omschrijving;"
End If
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )"
& _
" SELECT ""<description>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT "" <oia status=""+'""NOTINHIBITED"" optional=""false""
invertmatch=""false"" />' AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""</description>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<actions>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<input value=""+'""21[enter]"" row=""0""
col=""0"" movecursor=""true"" xlatehostkeys=""true"" encrypted=""false"" />'
AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""</actions>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<nextscreens timeout=""+'""0"" >' AS omschrijving;"
SCREEN1 = SCREEN1 + 1
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""<nextscreen name=""+'""'+""Screen""+""" & SCREEN1 &
"""+'"" />' AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""</nextscreens>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT ""</screen>"" AS omschrijving;"
DoCmd.RunSQL "INSERT INTO tabel_resultaat ( omschrijving )" & _
" SELECT "" "" AS omschrijving;"
next
 
Peter said:
I have created in VB some SQL statements in a for next loop.
After approx. 120 for/next loops, Access is not creating the correct result
anymore. There is a counter inside the for/next loop, but the counter is not
correct anymore after 120 (or sometimes more) loops.
Can it be that it is not possible to set a lot of SQL statements one after
the other? Must there be some brake?
Click to expand...

From the combination of your code and your complaint, yes, I get the
impression that the system needs some time. I know that queries normally
execute in a separate thread.

I have run more multi-line scripts, but without this problem.

DoEvents normally gives the system some breathing space.

Your INSERT INTO statements can use the VALUES clause instead of SELECT.
Does that matter? I haven't got the foggiest.

You can use currentdb.execute instead of docmd.runsql, I believe that is
generally advised.

What exactly is it a loop for? Because every statement in the loop
(well, apart from your if teller=1 clause, which I remember having
applied in some past) gets executed for every pass, and I haven't seen
any reference to the counter itself. Maybe there is a different approach
that doesn't produce this unwanted effect.

Mazzel
 
Bas,

Thanks for your suggestions.
I found the problem. The table which is created, has lost the following
order. So I created also an exta autonumber field. If I sort on this field
all data is present at the right order.

De beste wensen voor 2005!

Peter
 
Back
Top