Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving databases behind pivot tables

    I have a database (Access 2000) that I access through pivot tables in Excel 2000. When I take the database/Workbook home, I can no longer update the pivot tables. I have tried using a system DSN (which I create on both machines), and a file DSN (again, replicated on both machines). Is there any way other than going to each pivot table and manually editing the SQL statement to the correct address of the database? Why can't I just change the address of the database using the DSN?

    The SQL behind one of the tables, accessed through Microsoft Query is as follows:

    SELECT Main.record_id, Main.Authors, Main.Title, Main.Journal_full, Main.Journal_abb, Main.Vol, Main.Iss, Main.Start, Main.End, Main.PubYear, Main.Country_au, Main.Language, PrimaryDrug.PrimaryDrug, Indications.Indication
    FROM `F:Projects430001project`.Indications Indications, `F:Projects430001project`.Main Main, `F:Projects430001project`.PrimaryDrug PrimaryDrug
    WHERE Indications.record_id = Main.record_id AND Main.record_id = PrimaryDrug.record_id

    Kiwi44

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving databases behind pivot tables

    I have been able to change addresses by opening the query DQY as a text file. Usually, I only have had to back out some part of the address but it may work for you when you add extentions or whatever. You may also have to restablish the connection to the changed file through EXCEL. Its tricky. <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving databases behind pivot tables

    Thanks for your input - I realized it was problematic, and ended up editing the individual queries through Excel (I did not save them to .dqy files as far as I am aware). I also had to delete some pivot tables and recreate them on the second machine - I real pain!

    A nice feature for Excel 2002 would be the ability to change the location of external data sources for pivot tables. Microsoft, are you listening?

    In addition to moving the databases, it would also be helpful if I could rename the connections to a different database with the same structure - the user could click refresh, and the analysis would be done with the new data. Wouldn't that be nice?!

    kiwi44

Posting Permissions

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