Get external data from MS Access using variables

G

Guest

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
 
N

NickHK

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.
 
G

Guest

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(Array( _
"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 said:
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 said:
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
Click to expand...
Click to expand...
 
N

NickHK

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 said:
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(Array( _
"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 said:
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 said:
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.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using data from cells in a Query to a MS Access Database 2
Problem with external data and #reference error 2
Returning variables from external form 9
Open Access query from Excel using parameter 3
refresh data from MS Access DB not picking up changes to criteria 2
Query Data in Excel S/S from Access DB 2
Calculation on data from SQL database sing MS Quwry 1
passing criteria via Get External Data from a macro 2

Top