MikeN wrote:
It appears that you should just be able to update the data
source object (dsn) and all worksheets using it should be
updated. It doesn't seem to work that way. Once the
Workbook is saved, even if you delete the Data Source it
still connects using the old connection information.


Yes, I agree.
In my opinion the potential value of ODBC DSNs has been wasted by a bad
decision to store full connection information with the querytable.

One way around it would be to have the querytables remade each time you
open the workbook, using the DSN, along the lines of.

Sub Auto_Open()
Dim QT As QueryTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each QT In WS.QueryTables
QT.Connection = "ODBC;DSN=MyDatabase" ' + any params needed
QT.Refresh BackgroundQuery:=False
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup