Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external data
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 "Paul" wrote: |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. | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external data
Change contents of AcrtiveSheet.Querytables(1).Connection
HTH -- AP "Paul" a écrit dans le message de ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external
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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external
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 "Paul" wrote: | 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change database connection between live and test for external
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 "Ardus Petus" wrote: Change contents of AcrtiveSheet.Querytables(1).Connection HTH -- AP "Paul" a écrit dans le message de ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|