Sequential SQL queries using VBA

[hurryjet]

I'm wondering if someone could tell me the syntax for running multiple, sequential append queries using SQL in VBA. I have the syntax down for appending a single query using a command button:

Private Sub Command_Click
Dim strSQL as String
strSQL=SQL Statement (Insert into, Select, From, Where)
DoCmd.RunSQL strSQL
End Sub

But I need to do this a bunch of times in a row changing the criteria slightly each time. I tried revising the SQL as needed and then copying it below the first statement and putting the DoCmd statement after the second statement. But when I ran the code after that, only the first part of the query worked. I assume there has to be some way to string together repetitive SQL statements. Any advice would be greatly appreciated!

Leslie

[pbaldy]

Just keep repeating these two lines:

strSQL=SQL Statement (Insert into, Select, From, Where)
DoCmd.RunSQL strSQL

[hurryjet]

Hello Paul,

What a coincidence! I just printed out your very helpful tutorial on building SQL in VBA before visiting this site. Thanks so much for responding to my question.

I tried repeating the code (below) but all that ran was the first part. The two statements are identical except that in the second one a different field is referenced [HOUR16 POTASSIUM] instead of [HOUR16 OUTPUT]


Private Sub Command0_Click()
Dim strSQL As String
strSQL = "INSERT INTO [tbl_HOUR16_MISSING DATA] ( PTID, FIELD_ID, FIELD_LABEL, FIELD_PAGE, ERROR_CLASS )" & _
"SELECT tbl_HOUR16_FREQUENCY.[PTID], tbl_HOUR16_FIELD_INFORMATION.[FIELD_ID], tbl_HOUR16_FIELD_INFORMATION.[FIELD_LABEL], tbl_HOUR16_FIELD_INFORMATION.[FIELD_PAGE], tbl_HOUR16_FIELD_INFORMATION.[ERROR_CLASS]" & _
"FROM tbl_HOUR16_FIELD_INFORMATION INNER JOIN tbl_HOUR16_FREQUENCY ON tbl_HOUR16_FIELD_INFORMATION.[FIELD_ID] = tbl_HOUR16_FREQUENCY.[HOUR16 OUTPUT ID]" & _
"WHERE tbl_HOUR16_FREQUENCY.[H16 OUTPUT])=1;"
DoCmd.RunSQL strSQL

strSQL = "INSERT INTO [tbl_HOUR16_MISSING DATA] ( PTID, FIELD_ID, FIELD_LABEL, FIELD_PAGE, ERROR_CLASS )" & _
"SELECT tbl_HOUR16_FREQUENCY.[PTID], tbl_HOUR16_FIELD_INFORMATION.[FIELD_ID], tbl_HOUR16_FIELD_INFORMATION.[FIELD_LABEL], tbl_HOUR16_FIELD_INFORMATION.[FIELD_PAG]E, tbl_HOUR16_FIELD_INFORMATION.[ERROR_CLASS]" & _
"FROM tbl_HOUR16_FIELD_INFORMATION INNER JOIN tbl_HOUR16_FREQUENCY ON tbl_HOUR16_FIELD_INFORMATION.FIELD_ID = tbl_HOUR16_FREQUENCY.[HOUR16 POTASSIUM ID]" & _
"WHERE tbl_HOUR16_FREQUENCY.[H16 POTASSIUM])=1;"
DoCmd.RunSQL strSQL
End Sub

[pbaldy]

JasonM actually wrote that, but I found it helpful, so hopefully you do too. Here's another that may help here:

BaldyWeb-Immediate window

One thing I notice is a fairly common issue with spaces between lines, which can make the SQL either fail or be misinterpreted. In other words when you concatenate

[HOUR16 POTASSIUM ID]" & _
"WHERE tbl_HOUR16_FREQUENCY.[H16 POTASSIUM])=1;"

you end up with

"[HOUR16 POTASSIUM ID]WHERE tbl_HOUR16_FREQUENCY.[H16 POTASSIUM])=1;"

Note the lack of a space before the word "WHERE". Try fixing that and see if it helps. If not, can you post the db here? Conceptually what you have should work. I should ask, are there records that would match the second SQL?

[hurryjet]

Hi Paul,

Yes, I checked to make sure there were records that would match (I blush to say that that was only after the "double SQL statements" had failed a couple of times). I don't have the resources to work on this at home and am getting ready to leave, but will work with the spaces tomorrow and let you know what happens. Thanks again!

Leslie

[pbaldy]

No problem Leslie. Wish I could leave.

[hurryjet]

Hi Paul,

Gee, now I feel guilty because after I left I enjoyed an 18 "hole" round of disc golf. But I'm also very happy because I figured out the problem with running my VBA/SQL code--and this shows you what a total newbie I am. The code was fine. The problem was that somehow I had entered the code into two different environments, one its own module and one linked to the command button. I figured this out because I removed my "output" query from the environment I was working in and left only "potassium', clicked the command button and yet the table was updated with "output" records. Me thought something was fishy or supernatural or both. That inspired me to look at the code linked to the button and lo and behold it just contained "output." So I copied in Potassium and it worked like a charm. And then i got what for me is fancy at this stage and turned the warnings off (and then back on!!) and the table was updated beautifully and stealthily, like a thief in the night. I am very happy! Thank you so much for your help!

Hope you got to go home at some point!

Leslie

[pbaldy]

Happy to help Leslie, and welcome to the site by the way!

[hurryjet]

Paul,

Thank you! It is wonderful to have such helpful online resources. Back when I was first learning Access in the late 90s, one had to rely on written materials which typically didn't provide much help with specific problems. There was the advantage of being able to combine data management and body building, though, because one was forced to carry around enormous books, such as the Access Bible.

Leslie