How You Can Change To Another Connection Type In PowerPivot

I have noticed that it has been noted in a couple of places that once you choose to connect to data in a particular type of data source (i.e. an Access ACCDB file or a SQL Server or MySQL database) you are then subsequently unable to change the type of data source for PowerPivot data (say if you migrate your data from Access to SQL Server after building a big PowerPivot solution).  This could have major implications on a complex PowerPivot solution with lots of DAX functionality and Pivot Tables based on the existing tables. This would be a painful process no doubt :-(…

I have found, however, that  there IS a way to set up a Connection so the source CAN be changed.

Once again, to do that, you have to create the Connection from inside the Excel UI and then add it to the Data Model rather than from inside PowerPivot’s UI.

The secret is to use the generic OLEDB Connection from the “Other Sources” choice on the Data Tab (rather than the specific Access or SQL Server ones):

Connection Wizard

When you choose this option you can then choose the type of data you wish to connect to from a list of installed drivers on your machine (if for example you want to connect to something like MySQL you can just go to the MySQL site and download and install the necessary driver).

ConnectionType

If you choose, for example, SQL Server you can then step through a unique series of dialogs to find the server, database and table or view you are looking for.  Once selected remember that you have to choose to connect as a SQL statement rather than as a table (critical)

The secret is at this point is to:

1. Select the Command String text

2. Copy it to the clipboard (or you come back afterward and copy it once it is created)

3. Once you have clicked ok through and created your Connection you just copy what’s in the Clipboard to a cell in your spreadsheet .. like this:

Conenction Strings

Notice I did the same thing for a connection to an Access database using the ACE 12.0 provider.  I can have as many of these as I want or need.   I could also convert these strings to formulas (formulae ?) so I can type my variables (server, database, table) into other cells and concatenate them back together to create a functioning Command String (Cool eh?).  This way you can change your variables as easily as typing the path to the new Access database or the name of the new Server they moved your app to and the next time you run the code you’ll see below you’re connected up. Sweet !

Thankfully Excel generates these Connection Strings  for you for free so you don’t need to figure out all the parameters in there.  There are probably some that show up that you really don’t need because they’re just reflecting the default anyway.

But there is one I REALLY like to change – I always replace “Share Deny Write” with “Read” (not that it matters actually but it’s good form and MAY prevent collisions with other users of the source solution or PowerPivot – better safe than sorry).

So now I have a cell named ConnectionSQL and another ConnectionAccess (if you don’t know about or use Names now you’d better do it right now or you’ll be sorry some day – trust me :-)).

I can then create 2 very simple pieces of code (please forgive the formatting but I want you to be able to copy it):

Sub ConnectSQL()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionSQL”).Value

.Refresh

End With

End Sub

Sub ConnectAccess()

With ThisWorkbook.Connections(“<<Your Connection Name>>”).OLEDBConnection

.Connection = Range(“ConnectionAccess”).Value

.Refresh

End With

End Sub

So you can switch connections just by running one or the other Procedure (of course you could make it sexier but this is just to show you the functionality :-)).

After running one or the other sub check to see the change in the Connection String and also look at the change in the data in PowerPivot (if the tables are in fact different in some way – like more or less records – in the two sources).

Conclusion

I have been switching Connections around like this in Excel for probably 15 years.  It has allowed me to say “Yeah – no probs.” When a worried client says “Help  — IT moved our server !!!” ….  If it can change it will …

Now I know I can do the same with my PowerPivot data cache I am getting to the point where I wonder whether there’s anything we can’t do in Excel now (?) Nice thought eh?

Dick

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Excel 2013, Microsoft Excel 2013, Office 2013, PowerPivot, Uncategorized, VBA. Bookmark the permalink.

24 Responses to How You Can Change To Another Connection Type In PowerPivot

  1. I discovered some time ago that you can also store DAX queries in worksheet cells and change a query in a table similar to the process you show here.

    • Biggus Dickus says:

      Yep … That and linking a single cell range as a Linked table to be used as a variable in your DAX shows how you can drive this whole thing from the spreadsheet itself customizing results at run-time. Then you also have all the power of VBA to drive that process and to deal with the results (printing, requerying, etc….)

      Dick

  2. Radim Hampel says:

    Hi, we have a working solution leveraging odata api from azure appplication. But we had to rename most of the matadata (tables and fields), because api provided just technical names not useble for end users. Then we have to option to change the connection, which is pretty annoying, because in this scenario the user credentials are sent in url (hash). We are providing this solution to literally thousands of user and all of them have to change connection manually. Do you have any idea if we could overcome this somehow?

    • Biggus Dickus says:

      Sure … the same technqiue seems to work with DATAFEED Connections.

      1. I created an atom file called “TEST.atom” with this in it:

      Default

      Categories

      CustomerDemographics

      Customers

      Employees

      Order_Details

      2. I created a new Connection IN EXCEL using the OData Connection or the Data Connection Wizard (which I kinda prefer for its flexibility – but it’s probably six of one half dozen of the other) IN EXCEL NOT PowerPivot .
      3. I checked in PowerPivot and saw that there are 5 Tables added using the Data Feed.
      3. I opened the Data Connection IN EXCEL and copied the Connection String out and pasted it into a cell I named “ConnectionOData”
      4. Just for fun I also created a cell named “CommandtextOData” and typed just the word “Categories” into it.
      5. Then I created the following VBA Subroutine:

      Sub ConnectOData()

      With ThisWorkbook.Connections(“<>”).DataFeedConnection
      .Connection = Range(“ConnectionOData”).Value
      .CommandText = Range(“CommandTextOData”).Value
      .Refresh
      End With

      End Sub

      6. When I run the macro now I have only 1 table “Categories”.

      If I wanted to use a different .ATOM file I could just change it in the Connection String cell and rerun the macro or I could ask for different tables as well by vhanging the Command Text cell – or both.

      Remember – EVERYTHING YOU DO REGARDING THE CONNECTION ITSELF YOU MUST DO IT IN EXCEL.

      Sure you can add Measures and Calculated Columns and such to the tables and if you source a table with the same name from another source then they will be preserved.

      There ya go.

      Dick
      p.s. Where do I send the bill😉 Just kidding

    • Biggus Dickus says:

      I haven’t checked it but you might even be able to manage your user connections by pushing their URL into the Connection string on the spreadsheet (I hope). Don’t know how that works but it strikes me that it should.

      Dick

      • Radim Hampel says:

        Thank you for your effort. Did you try to change the imported tables/columns? I know that if could work but the trouble is when you rename them – Excel seems to refuse change the connection string (even visually you can see it – the connection properties dialog becomes disabled). When we started our solution, I was excited that this could be done, then we continued with development, renamed the tables and at the end of the day we realized that it has to be changed manually!

      • Biggus Dickus says:

        If you’re saying that you want to rename the tables once the Connection is changed then yes you are screwed. This is because if you change anything about table names or field names inside the PowerPivot UI then it locks them out from the Excel Object Model.

        That’s just the way it is:-). You are kind of pushing the envelope with this.

        Not sure if you could use the SQL oledb provider instead of Odata because then if you used the techniqie I wrote about you could redefine any connection, change field names on your Sql as the data cimes in and each table would a name lije Query, Query1 etc…. So no need to rename tables.

        Sounds like a major rewrite but it would work I would think.

  3. ild4nx says:

    Hi, I’m working on a similar issue but the powerpivot is now linked to a CSV file (Model OLE DB Query), and I want to move it to an Access (and, in the future, to an SQL) connection.
    I created the new connection as in your example (ACE 12.0) but when I’m trying to run the macro, this is not working. Now I’m wondering if is possible to change also this kind of connection and if yes, how. Thanks !

    • Biggus Dickus says:

      I’m afraid you need to give me more info😦 ..

      Maybe you can post you code here and I can have a look ?

      • ild4nx says:

        Thanks for your reply😉 and yes, you’re right, I provide not enought info !
        The actual connection is called “Text history” and it is a text file (csv), and I want to change in an Access connection (ACE 12.0, which is working perfectly)

        Here below the code I used:
        ————————————————————
        Sub ConnectAccess()

        With ThisWorkbook.Connections(“Text history”).TextConnection

        .Connection = Range(“ConnectionAccess”).Value ‘connection string copied from Excel
        .CommandType = xlCmdTable ‘new command type (previously Table Collection)
        .CommandText = Range(“CommandTextOData”).Value ‘table to look for

        .Refresh

        End With
        End Sub
        ————————————————————
        It returns the following error:
        Runtime Error ‘1004’:
        Application-defined or object-defined error

        Hope this is enought to let you help me.

        Thanks !

  4. Vincent says:

    I am getting the same error with similar code. I think that this is because you initially created your connection inside the PowerPivot interface instead of first creating it from Excel and then reference it from PowerPivot.

    This is what we did and we now have probably 50 pivot tables and graphs using that model. We are trying to move it from SQL Server to an ODBC source. I really wish that I knew that trick before I started.

    If anyone has a workaround for this, it would be appreciated. Is there a way to unlock the properties when the connection was first created in PowerPivot?

  5. Pingback: Excel 2013: Power Query OmniQueries. Dynamic And Refreshable Connections To The Data Model | Excel For You

  6. tkaresz says:

    Hy Dick,

    I made my connection based by your description, but for me looks like the connection string cannot be updated. I am getting error for the simple code below

    Sub updateconnection()
    a = ThisWorkbook.Connections(“Con4Araz1”).OLEDBConnection.Connection
    With ThisWorkbook.Connections(“Con4Araz1”).OLEDBConnection
    .Connection = a
    .Refresh
    End With
    End sub

    Any suggestion would be great
    Thanks

  7. Flybridge says:

    When I try this I get an error message when I run the VBA saying Run-time error ‘1004’ Method ‘Range’ of object ‘_Global’ failed. When I select Debug it highlights the .Connection line.

    Any ideas what I’m doing wrong?

  8. Branislav says:

    Hi Dick,

    We have PowerPivot data model connection set to “Oracle Provide for OLE DB” . Would it be possible (and if yes – How?), to change the data connection of the same model to target oData Data Feed ?

    Our goal is that the data feed completely reflects the current model structure and relationships, because otherwise it would not work. However, we wonder if it would be possible to preserve current rather complex models (contains different calculated fields, relationships etc) and change the connection type only?

    Thanks in advance!

    • Biggus Dickus says:

      That’s a tricky question because in some cases you CAN change the source and in others you can’t.

      First of all ANY possibility of changing CommandText or Connection source would only be possible IF YOU CREATED THE CONNECTION IN EXCEL RATHER THAN POWERPIVOT.

      And in Excel I have found switching from Access to SQL (if you used an OLEDB Connection) works fine. It does though appear to me that it is based on the Connection “Type” and it appears as though the Type cannot be changed from VBA. So while SQL and Access connections are “Type” 1 OData is “Type” 6 and I personally can’t see a way to change that.

      Sorry but that’s all I can provide at this point. Not sure if that helps you though😦 ..

      Dick

      • Biggus Dickus says:

        Further …. I would bet anything that this is exactly the same if your Connection is to Oracle and you want to switch it to OData.

        Dick

  9. Pingback: How To Change Connection Type In Power Pivot | Dick Moffat's Spreadsheet Blog

  10. sam says:

    @Dick
    A good practice from Excel 2013 onwards is always use Power Query to fetch the Data and load it to the Data model
    Its pretty easy to change the connection to a different data source in Power Query using a function that looks at the value of a cell

    • Biggus Dickus says:

      Hey Sam …

      I agree that you can use Powerquery/Get and Transform for this but I do not think it is so much as case of “a good practice” but rather “another way to do this”🙂 … I love me some PowerQuery and use it all the time where I have not got control over my data sources.

      I like this technique using Connection strings and VBA in that I tend to do most of my “transformations” in the source of my data (i.e. Access, SQL Server or SQL Azure) and then just bring the results of queries or functions directly into my tables inside the “Data Model”. Then I can drive changes to this from a table of Connections, Sources and SQL Statements. This way I recycle the same code and table and just set up my list and away we go.

      I find PowerQuery VERY useful for importing text reports and for transformations where I have no control over the source data.

      I do not agree that using PowerQuery is the only “good practice” and kind of wish you hadn’t put it that way. Let’s just say that there is another way that is equally complex but that also works🙂 .. ok?

      Dick

      • sam says:

        Transformations in Access / SQL Server are limited to what can be achieved via SQL
        Eg Try doing a Unpivot Other Columns in SQL – not easy

        I did a project recently where the Database was Access (for Testing) and SQL Server (for Production)

        In Power Query you could create a simple function

        let fGetDataDB=(QryNm)=>
        if fGetCellVal(“VBA_DB_TYPE”) = “SQLSERVER” then
        let
        Source = Sql.Database(fGetCellVal(“VBA_DBQ”), fGetCellVal(“VBA_PROVIDER”)),
        DATA= Source{[Schema=”dbo”,Item=QryNm]}[Data]
        in
        DATA
        else
        let
        Source = Access.Database(File.Contents(fGetCellVal(“VBA_FPN1″))),
        DATA = Source{[Schema=””,Item=QryNm]}[Data]
        in
        DATA
        in
        fGetDataDB

        The User could choose the Database in a cell drop-down and Power Query would pull data from that source

      • Biggus Dickus says:

        Exactly .. If you have no control of your data source and recieve it in a format that you need to UnPivot then that’s where I use PowerQuery …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s