How to Change a Power Pivot Connection from Access to SQL Server
Question details
The user needs to change an existing Power Pivot data connection from an Access database to a SQL Server database while retaining the existing data model.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to migrate the underlying data source of an established Power Pivot data model from Access to SQL Server.
- Observed behavior
- The Edit Connection dialog only permits selecting another database of the exact same type (Access), preventing a direct switch to SQL Server.
Before modifying connection properties or running VBA scripts, create a secure backup copy of your Excel workbook to ensure your data model and DAX measures are not accidentally lost.
Create a New SQL Server Connection and Rebuild the Model
Since Excel restricts changing data source types directly through the user interface, the safest and most reliable method is to establish a new connection and recreate the model relationships.
This approach guarantees a clean transition to SQL Server without risking underlying file corruption, though it requires manually redefining table relationships.
Navigate to the Power Pivot tab on the Excel ribbon and click 'Manage' to open the Power Pivot window.
Click 'From Database' under the Get External Data group, select 'From SQL Server', enter your server credentials, and import the required tables.
Switch to Diagram View in the Power Pivot window and manually drag and drop fields to recreate the relationships that existed in your Access model.
Once the new SQL Server data model is functioning correctly and measures are updated, go to Existing Connections and delete the legacy Access connection.

Modify the Connection String using VBA
Advanced users can attempt to force a connection type change by programmatically updating the Power Pivot connection string using a VBA macro.
Try WPS Office for Seamless Spreadsheet Management
If you are tired of dealing with complex data connection limitations and heavyweight add-ins in Microsoft Excel, consider switching to WPS Office. It provides a highly compatible, lightweight, and user-friendly alternative for all your spreadsheet and data analysis needs.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the downloaded file and follow the straightforward on-screen instructions to install the suite.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets to instantly open, edit, and analyze your existing Excel files.

Frequently Asked Questions
Why does Power Pivot restrict changing the data source to a different database type?
The Edit Connection dialog restricts changes to the same database type to ensure compatibility with the existing query structure and schema. Since SQL Server and Access use different query languages and drivers, Excel forces a strict structural match.
Will I lose my DAX formulas if I delete the Access connection?
Yes, if you delete the tables associated with the Access connection before migrating your DAX formulas, the measures will be lost. Always move or document your DAX calculations before deleting the original source tables.
Can Power Query handle data source changes better than Power Pivot?
Yes. If your data was imported using Power Query (Get & Transform Data) before being loaded into the Data Model, you can easily open the Advanced Editor in Power Query and manually update the M code to pull from a SQL Server database instead of Access.




