Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Query Refresh (03)

    I created a data query (to MS Access) and was wondering what the code would be to 'refresh' the query. I created the data query with the macro recorder on but do not see code on how to refresh the query.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    For example:

    Worksheets(1).Range("A10").QueryTable.Refresh

    or

    ActiveSheet.QueryTables(1).Refresh

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Refresh (03)

    Hans,

    Can you recommend a resource that can provide me the basics for manipulating the data query via code. This is the first time I have tried using the data query feature and I am feeling <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> . Well you get the picture.

    Thanks,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    See for example Querying External Data in Excel on Dick Kusleika's website - follow the links on the left hand side of the web page.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Refresh (03)

    Hans,

    Thanks for the link. After reading the information about data queries. I felt better in trying to manipulate the query by code. I got too smart for myself and now have a prompt which I can not get rid of.

    This line of code worked until I changed it:
    Sheet1.QueryTables(1).CommandText = Replace(Sheet1.QueryTables(1).CommandText, "='60056'", "='60061'")

    to

    Sheet1.QueryTables(1).CommandText = Replace(Sheet1.QueryTables(1).CommandText, "='60056'", "=?")


    If I remark out the second line of code and try to rerun it based on the first line or the line of code that did work, I still get the prompt.

    Any clues as to why?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    Once you have changed 60065 to ?, rerunning the original code won't change it back. Does it help if you execute
    <code>
    Sheet1.QueryTables(1).CommandText = Replace(Sheet1.QueryTables(1).CommandText, "=?", "='60056'")</code>

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Refresh (03)

    Hans,

    Instead of doing a "replace", is it possible just to set the field to a specific value such as 60056 or 60061? I have a list of department numbers that I want to extract data on.

    In theory I should be able to pass (one at a time) the department number, extract and then loop through the process until the last department number.

    I have been toying with the code and have not made any progress. Surfing the internet only provided the "replace" technique.

    Thanks,
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    Why not import the entire recordset, and then use available Excel methods such as formulas, pivot tables, whatever, to get the information you need?

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Refresh (03)

    Hans,

    Well it is a little bit more complicated where I am using ASP with an Access backend. Dumping the entire record set really won't work for me. I need to be able to pick and choose a variety of fields with specific data. The "replace" method works but I need to keep track of the last record extracted.

    Being curious, I was wondering if I would be able to change the fields with specific values.

    I'm attempting to start with only one field and it that works, then expand to a combination of fields.

    Regards,
    John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    Refreshing a querytable for each value involves a lot of overhead. Instead of using a querytable, it might be better to open a DAO or ADO recordset on the Access table and loop through its records.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Refresh (03)

    Hans,

    You are right ie too much overhead. I am trying ADO and can extract based on a WHERE.

    How does one extract on multiple fields? Such as WHERE [dept] = 1234 and [user] = me


    Thanks,
    John

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Refresh (03)

    More or less as you indicate, with the following rules:
    - Literal string values must be enclosed in quotes: ... [user] = 'John'
    - Literal date values must be enclosed in # characters: ... [orderdate] = #4/17/2005#
    - You can use AND and OR. AND has priority; use parentheses if needed.
    So your example would probably be

    ... WHERE [dept] = 1234 AND [user] = 'me'

Posting Permissions

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