Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing a Query from Excel (Access 2000)

    The user has access to Excel. I have written an query in Access. Can a user by only using Excel run the query in Access and get the query downloaded on their machine

    Thanks

    Mario

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

    Re: Accessing a Query from Excel (Access 2000)

    Yes, the user can use MSQuery to get data from an Access database, without having Access installed. See below for some limitations.

    1. <LI>In Excel, select Data/Get External Data/New Database Query...
      <LI>From the list of data sources, select one for MS Access databases. If there is one, click OK and continue with step 13.
      <LI>If there is none, select <new data source> and click OK.
      <LI>Enter a name (it's up to you, but it must not conflict with the names already in the list).
      <LI>Next, select the MS Access driver.
      <LI>Click Connect...
      <LI>Click Select...
      <LI>Find the database and click OK.
      <LI>Click OK again.
      <LI>If you want, you can select a table or query (you can leave this blank now and select it later).
      <LI>Click OK
      <LI>You should see the new data source selected in the list now. Click OK.
      <LI>Now, you can build a query based on the tables and queries in the database.
      <LI>Select fields, set a filter (optional), specify sort order (optional), finally click Finish.
      <LI>Back in Excel, select the cell to be the upper left corner of the imported data, and click OK.
    You'll see the External Data toolbar. Use this to refresh the data, edit the query etc.

    Note: MSQuery uses ODBC to get data from the database. ODBC doesn't support some Access features:
    <UL><LI>Parameter queries.
    <LI>Some built-in Access functions, such as Nz.
    <LI>Custom functions.[/list]HTH, Hans

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing a Query from Excel (Access 2000)

    Thanks for the idea, but MS Access is very, very slow. Any other ideas ?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing a Query from Excel (Access 2000)

    Your question isn't specific enough to answer. In what respect is Access slow? Do you mean that it takes a long time to retrieve the data in Excel or something about Access itself? There have been a number of threads on speeding up Access 2000 so a Lounge Search should get you some help on the latter. If you mean the query is slow, you will have to take a look at the query itself and also the number of records it returns.
    Charlotte

Posting Permissions

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