Change database connection between live and test for external data

[Guest]

I am using Office 2000. I have a test and live MySQL database on two
different servers. I have an odbc System DSN pointing to each DB. I have
created a nice report using Excel and the test DB. I cannot find where to
change the DSN for the queries in order to access the live data.

Where in excel is the DSN stored, and/or how can I change the DSN?

I searched the forums, and found how to do it with access, but they were
pointing to files, not ODBC type connections. The SQL editor has the database
name stored in the from clause, but I need to change the DSN, since the
database name stays the same, just the server changes.

 

[Dave Patrick]

Try using a DSN-less connection string. Something like;
http://www.connectionstrings.com/

Public Sub testing()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"
strSQL1 = "SELECT EmployeeID, LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!EmployeeID
Sheets("Sheet1").Range("B" & i) = rs1!LastName
Sheets("Sheet1").Range("C" & i) = rs1!FirstName
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I am using Office 2000. I have a test and live MySQL database on two
| different servers. I have an odbc System DSN pointing to each DB. I have
| created a nice report using Excel and the test DB. I cannot find where to
| change the DSN for the queries in order to access the live data.
|
| Where in excel is the DSN stored, and/or how can I change the DSN?
|
| I searched the forums, and found how to do it with access, but they were
| pointing to files, not ODBC type connections. The SQL editor has the
database
| name stored in the from clause, but I need to change the DSN, since the
| database name stays the same, just the server changes.
|

 

[Ardus Petus]

Change contents of AcrtiveSheet.Querytables(1).Connection

HTH

 

[Guest]

Dave:

Thanks for the reply. I should have been more specific.

I am not using Macro's or VBA, I am using "Get External Data." When you
define the query initially, it allows you to select your data source, but I
see no way to change it after the fact.

I tried to just change the "Host" parameter in the DSN, from the test to
live server, but evidentily Excel stores the entire DSN internally, and it
accesses the test DB anyway.

 

[Dave Patrick]

Then the other reply is correct. I never did like using the klutzy MSQuery


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Dave:
|
| Thanks for the reply. I should have been more specific.
|
| I am not using Macro's or VBA, I am using "Get External Data." When you
| define the query initially, it allows you to select your data source, but
I
| see no way to change it after the fact.
|
| I tried to just change the "Host" parameter in the DSN, from the test to
| live server, but evidentily Excel stores the entire DSN internally, and it
| accesses the test DB anyway.

 

[Guest]

Thank you for pointing me in a direction that works, however, I believe the
fact that I need to write a macro to do this is ludicrous.

Here is the macro I wrote to accomplish my task.

Sub ChangeConn()

Dim qt As QueryTable
Dim Wsh As Worksheet
Dim TestLoc As String
Dim LiveLoc As String
Dim LastSlash As Long

TestLoc = "testServer.domain.com"
LiveLoc = "liveServer.domain.com"

For Each Wsh In ThisWorkbook.Worksheets
For Each qt In Wsh.QueryTables
If InStr(1, qt.Connection, TestLoc) > 0 _
Then
qt.Connection = Replace(qt.Connection, TestLoc, LiveLoc)
Else
qt.Connection = Replace(qt.Connection, LiveLoc, TestLoc)
End If
qt.Refresh
Next qt
Next Wsh

End Sub