none
Update the connection with new Server and database name RRS feed

  • Question

  • Hello

    I'm using VBA to change the connection for an datamodel in Excel 2013.  I'm getting a runtime error 1004, when I supply the string to update the connection.  The line that fails is wcon.OLEDBConnection.Connection = cs.

    I used the follow blog as a guideline.  http://cwebbbi.wordpress.com/2013/01/06/parameterising-powerpivot-connection-strings-in-excel-2013/

    Sub ChangeCon()
    Dim mdl As ModelTable
    Dim wcon As WorkbookConnection
    Dim cs As String
    Servername = Sheet7.Range("b2").Value
    DatabaseName = Sheet7.Range("b3").Value
    cs = "OLEDB;Provider=SQLOLEDB.1;Data Source=" & Servername & ";Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=" & DatabaseName
    Set mdl = ActiveWorkbook.Model.ModelTables(1)
    Set wcon = mdl.SourceWorkbookConnection
    Debug.Print wcon.OLEDBConnection.Connection

    wcon.OLEDBConnection.Connection = cs

    Debug.Print wcon.OLEDBConnection.Connection
    mdl.SourceWorkbookConnection.Refresh
    End Sub

    Thanks

    Lee


    Lee Kohrman

    Sunday, May 26, 2013 9:09 PM

Answers

  • Hi Lee,

    Thanks for your email.

    I hope the following explanation helps you solve the problem.

    Since I didn't have two servers for the databases, I made the changes for database/table only in one server. The key point here is that when we connect to the SQL Server using Data Connection Wizard, we should uncheck the option "Connect to a specific table". 

    After that, in the "Select Table" dialog, we can select one table/query for the source. 

    After all is done, we can modify the code to change the connection as below:

    Sub ChangeCon()
    Dim mdl As ModelTable
    Dim wcon As WorkbookConnection
    Dim cs As String
    
    Set mdl = ActiveWorkbook.Model.ModelTables(3)
    Set wcon = mdl.SourceWorkbookConnection
    
    Debug.Print wcon.OLEDBConnection.Connection
    Debug.Print mdl.SourceName
    
    wcon.OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=YOYO-MSDN;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MSDN-WIN8-YOYO;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Test"
    wcon.OLEDBConnection.CommandText = "Products"
    wcon.OLEDBConnection.Refresh
    
    
    Debug.Print wcon.OLEDBConnection.Connection
    
    Set mdl = ActiveWorkbook.Model.ModelTables(1)
    mdl.SourceWorkbookConnection.Refresh
    
    End Sub

    Here we customize the OLEDBConnection's Connection string, CommmandText. We can also customize its CommandType as well. ( You can create a connection and open the connection file in the notepad to check all these information.)

    For the once more: 

    Set mdl = ActiveWorkbook.Model.ModelTables(1)

    It sounds wired in the code above that we have to set the mdl variable once more. I am still in cloud about this. If remove this code, an error will pomp up. Anyhow, I just keep it.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 29, 2013 12:11 PM
    Moderator

All replies

  • Hi Lee,

    Thanks for your post.

    Would you please provide the detailed error message for us? A screenshot of the error message would be appreciated. By the way, you can upload it via SkyDrive if you can't upload it directly in the post.

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 27, 2013 2:45 PM
    Moderator
  • Hi Lee,

    Thanks for your email.

    I hope the following explanation helps you solve the problem.

    Since I didn't have two servers for the databases, I made the changes for database/table only in one server. The key point here is that when we connect to the SQL Server using Data Connection Wizard, we should uncheck the option "Connect to a specific table". 

    After that, in the "Select Table" dialog, we can select one table/query for the source. 

    After all is done, we can modify the code to change the connection as below:

    Sub ChangeCon()
    Dim mdl As ModelTable
    Dim wcon As WorkbookConnection
    Dim cs As String
    
    Set mdl = ActiveWorkbook.Model.ModelTables(3)
    Set wcon = mdl.SourceWorkbookConnection
    
    Debug.Print wcon.OLEDBConnection.Connection
    Debug.Print mdl.SourceName
    
    wcon.OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=YOYO-MSDN;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MSDN-WIN8-YOYO;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Test"
    wcon.OLEDBConnection.CommandText = "Products"
    wcon.OLEDBConnection.Refresh
    
    
    Debug.Print wcon.OLEDBConnection.Connection
    
    Set mdl = ActiveWorkbook.Model.ModelTables(1)
    mdl.SourceWorkbookConnection.Refresh
    
    End Sub

    Here we customize the OLEDBConnection's Connection string, CommmandText. We can also customize its CommandType as well. ( You can create a connection and open the connection file in the notepad to check all these information.)

    For the once more: 

    Set mdl = ActiveWorkbook.Model.ModelTables(1)

    It sounds wired in the code above that we have to set the mdl variable once more. I am still in cloud about this. If remove this code, an error will pomp up. Anyhow, I just keep it.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 29, 2013 12:11 PM
    Moderator
  • Hi Lee,

    I temporarily marked the reply as answer and you can unmark it if it provides no help.

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, June 2, 2013 2:34 PM
    Moderator
  • any reason why I could not change the connection from an 2nd table to the first one? I'm using Power Query to import data to the model, however sometimes PQ just creates a 2nd connection and 2nd table in the model. the UI doesn't allow me to change PQ connections

    so I'm trying to use the above code to change the connection to something like 

    cs = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(c910a459-e9e7-451f-86b7-908383e1e7a0)$;Location=AppDetail;Extended Properties="UEsDBBQAAgAIAPJAXkNghcnIEQEAAPkAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAO29B2AcSZYlJi9tynt/SvVK1+B0oQiAYBMk2JBAEOzBiM3mkuwdaUcjKasqgcplVmVdZhZAzO2dvPfee++999577733ujudTif33/8/XGZkAWz2zkrayZ4hgKrIHz9+fB8/Iv7Hv/cffPx7vFuU6WVeN0W1/Oyj3fHOR2m+nFazYnnx2Ufr9nz74KPf4+jxy2z6NrvIU2q8bB69a4rPPpq37erR3btXV1fjq3vjqr64u7ezs3v39/7i+evpPF9k28WyabPlNP/IvjW7+a2Pjh7/pCBztDc+GN/b37833t3ZfXzXfPr4i2Jpft8d3x/f23v46Xjn8V3v48cn67Jd1/lRvtz+6vXju+bPx3d1GEf/D1BLAwQUAAIACADyQF5DD8rpqwgBAADpAAAAEwAcAFtDb250ZW50X1R5cGVzXS54bWwgohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAA7b0HYBxJliUmL23Ke39K9UrX4HShCIBgEyTYkEAQ7MGIzeaS7B1pRyMpqyqBymVWZV1mFkDM7Z28995777333nvvvfe6O51OJ/ff/z9cZmQBbPbOStrJniGAqsgfP358Hz8i/se/9x98/Hu8W5TpZV43RbX87KPd8c5Hab6cVrNiefHZR+v2fPvgo9/j6PGb61XepNR02Xz20bxtV4/u3m2m83yRNeNqlS/pm/OqXmQt/Vlf3F1l07fZRX53b2fn07vTatnmy3a7BYyPjh4/zc+zddmmp+/oY+mWXv8oPZF26Oqz
    j9r8XXuXP74bfWPRaZ+tVmUxzVr68u677UWzveA37zLiR/8PUEsDBBQAAgAIAARIXkOu5c4w9wEAAOECAAATABwARm9ybXVsYXMvU2VjdGlvbjEubSCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAADtvQdgHEmWJSYvbcp7f0r1StfgdKEIgGATJNiQQBDswYjN5pLsHWlHIymrKoHKZVZlXWYWQMztnbz33nvvvffee++997o7nU4n99//P1xmZAFs9s5K2smeIYCqyB8/fnwfPyKafNoW1TJ9LT93D3/j5DdOmnlW57P0u8Xy4Hi1epq3WVGmn6Vl3iYpPa+rdT3N6YPTd9O8HH+3qt9Oqurt1rOizMcn1bLNl22z9dHJo9/3qyavm993uV5W09/39dvrp3Vxmae/Z/pFMa2rpjpvf99nv8/u/u+7l25zX+mbqs3KlLpsft+H9NmrfFXVbcON+FP9ZPyubN59dOfOiLH58Y/oq/R50bQfEUqC2y/+3otskX/mvvn+L/ne06zNvi+vvMoX1WU+e1bUTfuqumrovTfZhJB//bZYbXkAR/e0E9P0uPl2ns3y2r7xsq4WVZvLp81WF7K+fjLPlhf57M31KrdvvqmzZXNe1YuTqlwvlviu2er2M/rFv/ij51nTfrWaZW1OQ8g/GqUtwODvXzJKf/FHL9eTsmjmae/LX6KdK07SjRurfKyfbnkYjn7xRyevvki/XIHW62XRXqdfvXr+URzc7gC8sBWBfJpfpq9bwZH/+KliRb+mH0mT/R18rr/ver/veb/f837f936/T8glxTKG3uH/A1BLAQItABQAAgAIAPJAXkNghcnIEQEAAPkAAAASAAAAAAAAAAAAAAAAAAAAAABDb25maWcvUGFja2FnZS54bWxQSwECLQAUAAIACADyQF5DD8rpqwgBAADpAAAAEwAAAAAAAAAAAAAAAABdAQAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQAAgAIAARIXkOu5c4w9wE
    AAOECAAATAAAAAAAAAAAAAAAAALICAABGb3JtdWxhcy9TZWN0aW9uMS5tUEsFBgAAAAADAAMAwgAAAPYEAAAAAA=="

    wcon1.OLEDBConnection.Connection = cs

    however I get run-timer error 1004 at this point.

    any hint?

    Tuesday, November 5, 2013 3:17 AM