Results 1 to 4 of 4
  1. #1
    rjbautista20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    23

    Change Pivot Data Source from MS Access to SQL Server

    Hi Good day,



    my Access database is about to reached the maximum capacity of 2GB. what i did to is used the Upsizing wizard default in MS Access to move all my table to SQL server.

    I successfully migrated all my tables, but i'm struggling in changing all my pivot table data source from MS Access to SQL server.

    i'm having this error. "you cannot choose a connection file that is incompatible with the existing connection".

    all in all i have at least 100 pivot in place. and i don't like to re-work on this because i might forgot some formula in the existing one.


    hope you can help me.

    Thanks
    rjbautista20

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is the db really that big? Have you run Compact and Repair? I presume you are not storing images in Attachment field because the conversion was mostly successful. Only issue is with tables set up as pivots? Be aware MS has removed pivot table/chart feature from Access 2013.

    Googled: Access pivot table sql
    Here is one http://stackoverflow.com/questions/6...-to-sql-server
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rjbautista20 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    23
    Hi June7,

    yes, the db was really big. we used to download the data. and we will upload the it to MS Access every day and monthly basis. All my Pivot are in Excel File and only connected to MS Access Database. what i want to do now, is to change the data source from MS Access to SQL server.


    Thanks

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You have Excel pull data from Access to pivot the data? Seems to me Excel should still be able to pull the data from the Access table links.

    You want to connect Excel directly to the SQL db?

    I googled: you cannot choose a connection file that is incompatible with the existing connection
    Here is one
    http://www.tech-archive.net/Archive/.../msg00010.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Text Box Data Source On Open Event
    By mcktigger in forum Programming
    Replies: 1
    Last Post: 01-18-2013, 12:24 PM
  2. Replies: 2
    Last Post: 10-12-2012, 03:46 PM
  3. Update Pivot Table Record Source via VBA
    By GeekInOhio in forum Access
    Replies: 0
    Last Post: 09-06-2012, 02:26 PM
  4. Change Row Source
    By NISMOJim in forum Forms
    Replies: 8
    Last Post: 08-31-2012, 10:17 PM
  5. Replies: 1
    Last Post: 05-04-2012, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums