Get external data from MS Access using variables
 

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

Get external data from MS Access using variables
 

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

Get external data from MS Access using variables
 

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

Get external data from MS Access using variables
 

RTP,
Do you need to recreate the query everytime ?
It would be easier to use 2 parameters that update the query each time they
change.
Check out "parameter_query" in Excel help.

Also it more readable if you do not use spaces in the various objects names;
then you do not need all the ` characters.
And whilst the & Chr(13) & "" & Chr(10) can format you query nicely, here
they achieve nothing

NickHK

"RTP" wrote in message
...
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