Query works but VBA fails

[dumbledown]

Hi, I have a query that works fine:



SELECT *
FROM [outputlinks1]
UNION ALL
SELECT * FROM [outputlinks2];

I have VBA code of this query that fails (is not recognised):

Dim strSQL As String
strSQL = "SELECT * " & _
"FROM [outputlinks1] " & _
"UNION ALL " & _
"SELECT * " & _
"FROM [outputlinks2];"
DoCmd.RunSQL strSQL


I haven't a clue why. Does anyone know??

[pbaldy]

You can only "run" an action query, not a select query. Since it's not dynamic, why not just leave it as a saved query?

[dumbledown]

Thanks pbaldy,
OK that makes sense.
However, VBA is required as I want to add a loop in as the number of tables to be linked varies from use to use.
The code I have is:

Dim strSQL As String


strSQL = "SELECT " & Chr(42) & " " & _
"INTO newlinks " & _
"FROM [outputlinks1]"
For i = 2 To logs
strSQL = strSQL + " UNION ALL SELECT " & Chr(42) & " FROM [outputlinks" & i & "]"
Next i
strSQL = strSQL + ";"


Debug.Print strSQL
DoCmd.RunSQL strSQL


When run through the debugger for logs = 3 the SQL is:

SELECT * INTO newlinks FROM [outputlinks1] UNION ALL SELECT * FROM [outputlinks2] UNION ALL SELECT * FROM [outputlinks3];

Do you know a work around for this?

[orange]

As Paul said

You can only "run" an action query, not a select query.

.

What did you plan to do with the result?

You could use it to open a recordset.

[dumbledown]

Thanks Orange,
I want to combine the tables (this number varies) to eventually average the combined data outputted into a table (ideally an existing one) so it can be linked to another program.
Hope that makes sense??

[pbaldy]

You could use the SQL as part of an append or make table query, if that's what you want.

[dumbledown]

Call me an idiot, but aren't I doing that with the INTO statement?

[pbaldy]

I'm the idiot.

My apologies, I only looked at the original SQL. That is certainly a make table query. I've never tried with a union. What error message do you get? If you run the SQL in a query, do you get a better error?

[dumbledown]

No worries...
'An action query cannot be used as a row source'
Thanks for persisting!

[pbaldy]

Odd, since you're not trying to use it as a row source (if that's all the code). It occurs to me the final syntax might need to be

SELECT * INTO newlinks FROM (SELECT * FROM [outputlinks1] UNION ALL SELECT * FROM [outputlinks2] UNION ALL SELECT * FROM [outputlinks3];

If that doesn't work, can you post the db here?

[dumbledown]

Mr Baldy
Thanks very much for that - it works perfectly, and works in VBA as well!
Great stuff, have a top weekend

[pbaldy]

You too! You must have picked up on the fact that I missed the closing parentheses. I'm having a bad day here!