PDA

View Full Version : VBA Change ODBC Connection String (Excel)



hs140
02-22-2014, 02:17 AM
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
02-22-2014, 04:14 AM
What database is it? Doesn't the DSN point at the database, maybe you need multiple DSN.

hs140
02-22-2014, 04:16 AM
What database is it? Doesn't the DSN point at the database, maybe you need multiple DSN.

Hi its a SQL database

Bob Phillips
02-22-2014, 04:48 AM
I assume that you mean SQL Server? Did you see my other comment?

hs140
02-22-2014, 05:57 AM
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
02-22-2014, 06:14 AM
ARe you sure that the DSN does not use a specific database name, it is referrring to the server?

hs140
02-23-2014, 04:16 AM
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
02-23-2014, 08:04 AM
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