Workbook Connections - Dynamic Data Source

[MahmoudSa]

Hello Again,
Is it possible to manipulate the connection string either through functions of VBA code, in order to be Dynamic rather than static ?
In other words, here is the connection string I currently have
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DBName;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LocalPC;Use Encryption for Data=False;Tag with column collation when possible=False

The only parameter that I want to pass is the "Initial Catalog". It should retrieve data from a predefined drop down list within the same excel file.

Looking forward to hearing from you,

 

[Fazza]

Hello Mahmoud

I expect that to be simple. It is just a text manipulation.

One way

strConn = replace$("Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DBName;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LocalPC;Use Encryption for Data=False;Tag with column collation when possible=False","DBName", worksheet_reference.range("cell_with_dropdown_list").value2)

regards

 

[MahmoudSa]

This function is to be filled out under the connection string ? How about the parameter "Value 2", where does it retrieve data from ?


Looking forward to hearing from you
Best Regards,

 

[MahmoudSa]

Any feedback ?

 

[Fazza]

The worksheet_reference.range("cell_with_dropdown_list").value2 retrieves the data from the cell with the dropdown list. The code line gives the required connection string.

 

[MahmoudSa]

Hello Fazza
Excuse me as I am really not being able to apply the method you proposed.
I followed the steps below:
1- I created an SQL view which retrieves all the possible databases names
2- I created a drop down list cell, the choices are derived from the data set above (the data set reference is "Companies")
3- I went to the current data sources (Data Tab > Connections),I clicked on the connection string that need to be modified, and then "Properties"
4- On the Definition tab of the connection string, under Connection String:, i put the following
strConn = replace$("Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DBNAME;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MAHMOUDSA;Use Encryption for Data=False;Tag with column collation when possible=False",DBNAME, Companies.value1)

It is not working !
I am quite sure I am not figuring this out.
Please guide me more thoroughly to get this resolved.

Thanks in Advance,

 

[Fazza]

hello, Mahmoud

what I posted was code to modify the connection string. it is only usable in VBA

it doesn't change a connection. (I understood the question was how to change the connection string, not change the connection)

if you want to change the connection for something, I don't know what that something is. so don't know what object to refer to within VBA code

for VBA code, you'll want something like

strConn = like posted above
object_that_uses_the_connection.connection = strConn

OK? btw, that could be just one line of code

what (object) uses the connection?

maybe the macro recorder will show you. is that familiar to you?

 

[Fazza]

hello, Mahmoud

Via Google I found Excel macro to change external data query connections - e.g. point from one database to another - Stack Overflow

In particular, see the code

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">'''' Sets all external data connection strings to the given value (regardless of whether they're
'''' currently ODBC or OLEDB connections. Appears to change type successfully.
Sub UpdateAllQueryTableConnections(ConnectionString As String)


    Dim w As Worksheet, qt As QueryTable
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection


    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection
            odbcCn.SavePassword = True
            odbcCn.Connection = ConnectionString
        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.SavePassword = True
            oledbCn.Connection = ConnectionString
        End If
    Next
End Sub

regards</code>