SQL Syntax Error: Incomplete Query Clause

[wjyoung]

I imported data to Excel using (Data > Import External Data). I then created a VBA macro that prompts the user for the query limit that will be used to refresh the query. I assembly the SQL command in a string variable and use that variable as the argument for .commandtext.

With Selection.QueryTable
.Connection = Array(Array("ODBC;DBQ=Z:\My Database.mdb;DefaultDir=Z:\;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"))
.CommandText = Array("mySQLstring")
.Refresh BackgroundQuery:=False
End With

I create the new query limit using a designated cell in the spreadsheet. The objective is to find records that begin with the characters entered into that designated cell (e.g. 123):

myPartType = Range("Query_Limit").Value & "%"

Here is my string variable statement:

mySQLstring = "SELECT " & _
"[My Table].[Field 1], " & _
"[My Table].[Field 2], " & _
"[My Table].[Field 3]" & _
Chr(13) & "" & Chr(10) & _
"FROM 'Z:\My Database.mdb'.[My Table] [My Table]" & _
Chr(13) & "" & Chr(10) & _
"WHERE ([My Table].[Field 1] Like '" & myPartType & "')" _
& Chr(13) & "" & Chr(10) & _
"ORDER BY " & _
"[My Table].[Field 1], " & _
"[My Table].[Field 2], " & _
"[My Table].[Field 3]"

The next line is:

Range("A1").Value = mySQLstring

Here is what I copied from cell A1:

SELECT [My Table].[Field 1], [My Table].[Field 2], [My Table].[Field 3]
FROM 'Z:\My Database.mdb'.[My Table] [My Table]
WHERE ([My Table].[Field 1] Like '123%')
ORDER BY [My Table].[Field 1], [My Table].[Field 2], [My Table].[Field 3]

I get a SQL syntax error when I reach the .Refresh command. When I enter the SQL statement into Access, I see the particular syntax error is "Incomplete Query Clause".

Does anyone see what I am doing wrong?

 

[pbaldy]

I haven't worked from Excel, but I would think that the connection string handles the file location, so does this work?

FROM [My Table]

 

[wjyoung]

Replying to my own thread...

I forgot to mention that at first I did not have the quotes surrounding mySQLstring, in the .commandtext statement:

.CommandText = Array(mySQLstring)

I tried dimensioning mySQLstring as a string and as a variant and got the same error: Type Mismatch.

I'm using Array() because this is what was returned when I used the macro recorder.

When I try this:

.CommandText = mySQLstring

I get a general ODBC error (1004) on the .Refresh command.