Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2010
    Location
    Bramham, West Yorkshire, England
    Posts
    59
    Thanks
    34
    Thanked 0 Times in 0 Posts

    Getting data from an Access-linked table into Excel

    I have an Access 2003 database that has linked tables, linked back to an Oracle database. I need to create a dynamic Excel pivot report that pulls in up to date data from one of the tables. Unfortunately, I'm not able to get access to the Oracle data source.

    However, using the 'Get External Data' facility fails, as Excel (2010) seems to be unable to 'see' any linked tables.

    I've been advised that I should create a seperate copy of the table and use an update query to update the second (non-linked) table. This can then be pulled into Excel. I reckon I can do that without a problem.

    My issue would be ensuring that the update query updates the secondary table at regular intervals, once
    a day would be fine. Can anyone point me in the right direction. Thanks in confusion.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Do you mean an Append table rather than an Update query. An Append query is used to copy records into a table from another table or query.

  3. #3
    Star Lounger
    Join Date
    Aug 2010
    Location
    Bramham, West Yorkshire, England
    Posts
    59
    Thanks
    34
    Thanked 0 Times in 0 Posts
    No. Was advised to create a copy of the linked table, the build an update query, that would update the copied table withdate from the linked table.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I think you would find it easier to have the Access database use Automation to update the data in the Excel workbook, which would then cause the pivot results to be updated. That assumes that the Access database is always up and running. Check out the tutorial Automation 101 and the more detailed link Using Excel 2007 functions in Access 2007. Note that the principals involved work from Office 2000 forward, so setting a reference to Excel 2010 in Access 2003 should be OK.
    Wendell

  5. The Following User Says Thank You to WendellB For This Useful Post:

    hubyster (2013-09-08)

  6. #5
    Star Lounger
    Join Date
    Aug 2010
    Location
    Bramham, West Yorkshire, England
    Posts
    59
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Thanks. I'll give that a serious look.

Posting Permissions

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