+ Reply to Thread
Results 1 to 4 of 4

Get external data from MS Access using variables

  1. #1
    RTP
    Guest

    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

  2. #2
    NickHK
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    RTP
    Guest

    Re: 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(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" 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  4. #4
    NickHK
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1