VBA Change ODBC Connection String (Excel)

[hs140]

Hi I have a spreadsheet which looks at an ODBC link. The connection string works fine which is

DSN=xxxx;UID=XX;PWD=xxx;APP=Microsoft Office 2010;WSID=xxx;DATABASE=test;

The x i have hidden the names. My problem is i want to change the name of the database which is test in this example to another database name eg live.

Manually i can do this, but i want the user to select the database they want, once they have selected the database the test will change to the database the user has decided.

I can change the string in code but when i run this it does not change this.

Can anyone please guide me in the right direction please.

Thanks

[Bob Phillips]

What database is it? Doesn't the DSN point at the database, maybe you need multiple DSN.

[hs140]

What database is it? Doesn't the DSN point at the database, maybe you need multiple DSN.

Hi its a SQL database

[Bob Phillips]

I assume that you mean SQL Server? Did you see my other comment?

[hs140]

I assume that you mean SQL Server? Did you see my other comment?

Yes its SQL Server, in total there could be 15 databases, which the user could connect. The DSN does point to the right database.

I have tried this code, by changing the connectionstring database to test2 but nothing has been changed.

Dim con As New ADODB.Connection
con.ConnectionString = "DSN=xxxx;UID=XX;PWD=xxx;APP=Microsoft Office 2010;WSID=xxx;DATABASE=test2;"


Thanks

[Bob Phillips]

ARe you sure that the DSN does not use a specific database name, it is referrring to the server?

[hs140]

Hi the DSN is looking at the server.

I found this code which works (see below) from another forum by Jerry Sullivan, where you can change the database. It only works once because it has constant the string stays the same, i tried changing the code to having the database as a variable but this did not work. As i have 15 database with the same structure on the same server, i want to swap between database to find my results, only thing which needs changing is the database name.

I have pasted the code

Sub SwitchODBCSource()
    Dim conn As WorkbookConnection
    Dim sOldConnection As String, sNewConnection As String
    
    Const sOldPath As String = "DSN=xxxx;UID=XX;PWD=xxx;APP=Microsoft Office 2010;WSID=xxx;DATABASE=test2; '--omit trailing backslashes to change DefaultDir
    Const sNewPath As String = "DSN=xxxx;UID=XX;PWD=xxx;APP=Microsoft Office 2010;WSID=xxx;DATABASE=test2;"
    
    For Each conn In ActiveWorkbook.Connections
        With conn
            If .Type = xlConnectionTypeODBC Then
                sOldConnection = .ODBCConnection.Connection
                If InStr(1, sOldConnection, sOldPath) > 0 Then
                    sNewConnection = Replace(sOldConnection, _
                            sOldPath, sNewPath, Compare:=vbTextCompare)
                    .ODBCConnection.Connection = sNewConnection
                    .Refresh '--optional to refresh now
                End If
            End If
          End With
    Next conn
    
    Set conn = Nothing

End Sub

[Bob Phillips]

I have never used this technique in anger, but it seems to me that you should be able to connect, then change the connection database to your target db.

I created a DSN that was pointing at AdventureWorksDW2008R2, then ran the following code to point it at one of my dbs, seems to work fine. See if you can adapt this.

Dim conn As Object 'ADODB.Connection
Dim RS As Object 'ADODB.RecordSet
Dim data As Variant
     
    Const connPath As String = "DSN=myDSN"
    
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connPath

    Set RS = CreateObject("ADODB.Recordset")
    
    Set RS = conn.Execute("SELECT * FROM DimProduct")
    data = RS.GetRows()
    MsgBox data(1, 0)

    conn.DefaultDatabase = "BARS"
    
    Set RS = conn.Execute("SELECT * FROM [User]")
    data = RS.GetRows()
    MsgBox data(1, 0) & " " & data(2, 0)
     
    Set conn = Nothing