Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table - Access source location changes (Excel XP)

    I need to test updated pivot tables with an updated Access database in a test environment. After testing, the pivot tables will have to refer to the Access database located on another server. I do not see where I can change the reference to the data source (I

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Pivot Table - Access source location changes (Excel XP)

    A File DSN was created with the original pivot table - that information is not maintained within Excel. If you go out to Control Panel, look for the ODBC applet. Might also be called Data Sources. Look under the File DSN tab for the DSN, you can open it and edit it from here.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Access source location changes (Excel XP)

    Catherine, thanks for your response. Unfortunately there is no DSN for this workbook. When it was created the exact path for the database was used.

    Since the user's current Excel file is referencing the "M:" drive for the Access database, I will map my "M:" drive to the server with the test data. When the user is given the updated file on his pc, it will reference the correct database.
    Judith

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table - Access source location changes (Excel XP)

    I was doing something similar with Excel 2000 and a pivot table based on an OLAP cube in SQL Analysis Services. The best thing I found was VBA code... something along the lines of:

    ActiveWorkbook.PivotCaches(1).Connection = strNewConnectionString

    There's a little more info in this post:

    http://www.wopr.com/cgi-bin/w3t/showthread...vc=1#Post130528
    Edited by WebGenii - try this <!post=link,132026>link<!/post> to the above
    JMac

Posting Permissions

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