Nick,

Thanks for the info. I am still having a problem, I ahve pasted my code.
It will probably just be a syntax error, can you help.

Sub aaaagetdata()
'
' aaaagetdata Macro
' Macro recorded 10/05/2006 by rpeet
'
v_name = "Mr Smith"
v_loc = "UK"
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=D:\Documents and Settings\My
Documents\Access_Data.mdb;DefaultDir=D:\Documents and Settin" _
), Array( _
"gs\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `Access Data`.NAME, `Access Data`.LOCATION" & Chr(13) & "" & Chr(10)
& "FROM `D:\Documents and SettingsMy Documents\Access_Data`.`Access Data`
`Access Data`" & Chr(13) & "" & Chr(10) & "WHERE ('Access Data'.NAME=" &
Chr(34) & v_name & Chr(34) & ") AND ('Access Data'.LOCATION=" & Chr(34) &
v_loc & Chr(34) & ")

.Name = "Query from MS Access Database_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

End Sub

"NickHK" wrote:

RTP,
Beacuse your variables are currently within the string, they are not being
seen as variable by VBA and hence not evaluated. So if you do:
Debug.Print "WHERE (Access_data.NAME=v_name AND Access_data.LOCATION=v_loc)"
you get:
WHERE (Access_data.NAME=v_name AND Access_data.LOCATION=v_loc)
Which is not what you are after.
So, you have to end your string, add the value of the variable, continue
with the string:
"WHERE (Access_data.NAME=" & v_name & " AND Access.....
But you also need to add the " around the variable as Access expects string
to be surrounded by quotes (or single quotes ', Chr(39)). So:
"WHERE (Access_data.NAME=" & Chr(34) & v_name & Chr(34) & " AND Access.....

NickHK
P.S. Notice I dropped the bracket and comma, which I suppose comes from the
Array(....) that VBA generates for these queries, but is not usually
neccessary, unless your SQL exceeds the limit (1024 characters ??)
Also, I tend to avoid giving fields names that may conflict with some
property etc that is used. Name, Date, Count, Item etc can be used in Access
(e.g. as [Date]), but to avoid confusion, of yourself and the software, use
names that are more descriptive.


"RTP" wrote in message
...
I am trying to import external data into Excel from Access using the
database
query technique. I have recorded a macro that pulls in data based on
specific criteria, the following is the criteria I have used:


"WHERE (Access_data.NAME='Mr Smith') AND " ,
"(Access_data.LOCATION='UK')")

This works sucessfully (and returns four records) but what I need to do is
replace the 'hard coded' criteria with varaibles, like this:

v_name = "Mr Smith"
v_loc = "UK"

"WHERE (Access_data.NAME=v_name AND " , "(Access_data.LOCATION=v_loc)")

This method does not return any records becasue I am using variables. If
I
hard-code the variables the query works.

How can I successfully get external data from an Access database using
variables as selection criteria?

Thanks
RTP