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.
|