Results 1 to 11 of 11
  1. #1
    David Cristallo
    Guest

    Querying Access db from Excel

    Hi Excellers,

    I know this is not really an Excel subject but don't crucify me...
    Let's say, I've got one access table, tblCountries, with a list of countries and their associated abbreviation and ISO code. From Excel, I would like to retrieve, for a given country abbreviation, the corresponding ISO code. Is it possible to retrieve this information without using a recordset to store the returned ISO code ?

    TIA,
    David

    Access 97 & Excel 97 under NT4
    Excel 2000 & Access 2000 under NT5

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Access db from Excel

    I am not quite sure if you want to know if you can do this from Excel or from Access. In Excel you can use the formula VLOOKUP(Countrycode,Range,2,false) to find th corresponding ISO code of the countrycode, assuming that you search in the "Range" in the second column and you want an exact fit (= false).

  3. #3
    David Cristallo
    Guest

    Re: Querying Access db from Excel

    Hi Hans,

    To clarify my question, I just need to query an Access database from Excel and my question is: is it possible to retrieve a single value using a SQL query with the DAO technology or do I need to use the recordset object to achieve this task ? I would of course prefer a solution that does not require any recordset object. Any idea ?

    TIA,
    David

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Access db from Excel

    I think you either need to use a recordset object or you can try communicating directly from Excel to your Access application via DDE. I don't know what the possibilities are in Excel via Data >> Get External Data >> Create New Query.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Querying Access db from Excel

    Are you referrring to DAO because you already have a VBA program into which this requirement must be incorporated?

    If you just want to retrieve values using the user interface (no programming), you can use Data, Get External Data, Create New Query... from the Excel menu and follow the prompts.

    Jeremy

  6. #6
    David Cristallo
    Guest

    Re: Querying Access db from Excel

    Hi Jeremy,

    I do need to use DAO because I implement query functionalities in my Excel project.

    David

  7. #7
    David Cristallo
    Guest

    Re: Querying Access db from Excel

    I will investigate the DDE possibilities.

    Thanks for your help,
    David

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Querying Access db from Excel

    Hi David,
    Just as a matter of interest, why don't you want to use a recordset? To my mind, even if you only retrieve one field value, it's still a recordset.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    David Cristallo
    Guest

    Re: Querying Access db from Excel

    Hi Rory,

    I've been told that the use of a recordset would decrease the performance of my procedure. That's why I would like to know if I can instead use a SQL query that would return its result in a private variable defined in my proc. I just want to implement the best solution...

    David

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Querying Access db from Excel

    Hi David,
    Simple answer is that an SQL query will, by definition, return a recordset. If your query is written to only return one field (the ISO code as I recall) then I don't imagine this will result in a performance hit anyway - you open the recordset, assign the value to a variable or cell and then immediately destroy the recordset. How you write it though would depend on how your app works - if you're going to be doing the same thing for a lot of different countries (i.e. running the same query over and over again) it might be easiest to run it once to retrieve all the data and then store the data in an array or on a worksheet. You can then use lookups to retrieve the ISO code for each required country.
    Does that help?
    I use this exact procedure in a number of my workbooks and, just for info, one of them runs a query 52 times (for different lines of business) and then enters the info into various cells on 52 different sheets. On a PII 233 on NT4 network it took about 20 seconds or so.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    David Cristallo
    Guest

    Re: Querying Access db from Excel

    Thank you for your help Rory. Now I know the appropriate technique that should be implemented.

    Thanks again,
    David

Posting Permissions

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