Hi

I see two potential problems. Apoloigies if you know this already:

VBA strings start and end with the sign " , so if you need " in the middle
of the string you have to write it different. This will generate an error:

Dim StrSQL As String
StrSQL = "They call me "Phantom""
MsgBox StrSQL

because the string ends at the " before Phantom. This will work:

Dim StrSQL As String
StrSQL = "They call me ""Phantom"""
MsgBox StrSQL

Standard SQL use single quotes around string variables and nothing around
numeric variables. If Age is a numeric field then the syntax would be

WHERE (Name='An' OR Name='John') And Age=26

This should also work

WHERE Name IN ('An','John') And Age=26

HTH. Best wishes Harald

"Wim" skrev i melding
oups.com...
I try to run an SQL query in VBA code, to perform a query on an Excel
table and have the result added as an Excel table in the same sheet (to
range j1). (see code below) The SELECT statement comes into the
variable strQuery.

Everything works fine if I use the Microsoft Query syntax for the
query.

Eg in a simplified version that syntax would be:

(1) SELECT ... FROM ... WHERE Name="An" And Age="26" Or Name="John" and
Age="26"

But it doesn't work with the following statement, which in SQL means
the same:

(2) SELECT ... FROM ... WHERE (Name="An" Or Name="John") and Age="26"

If I go into the Microsoft Query program and I enter manually query (2)
in the SQL editor, it translates it automatically into query (1), so
there query (2) does work.
However, in my VBA code I assign the query (2) to strQuery, and then I
get an error message. As I said, I don't get this error message with
query (1). It seems in the VBA code I can only assign the (1) syntax to
strQuery, because the code doesn't know how to handle syntax (2).

Now you may say, why don't you just work with a query syntax like (1)?
Because with much more complicated queries, queries like (2) are much
shorter and easy to build.

Anyone knows how to solve this problem?

With

ActiveSheet.QueryTables.Add(Connection:=Array(Arra y("ODBC;DSN=Excel-bestande
n;DBQ=C:\test.xls;DefaultDir=C:"),
;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")) ,
Destination:=Range("j1"))
strQuery = ... '
here comes the SELECT statement
.CommandText = Array(strQuery)
.Name = "Query1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With