Changing data source passwords in workbook ?

  • Thread starter Thread starter MikeN
  • Start date Start date
M

MikeN

My company has just implemented expiring passwords. Some
of my users have created Excell workbooks containing
multiple worksheets, each retrieving data from an external
database.

This means that when the password expires the user will
have to change the password in each worksheet. (In some
cases 20 sheets to a book)

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.

I need to find a simpler way for my users to update their
queries when their passwords expire.
..
 
MikeN said:
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.
Click to expand...

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
 
Back
Top