Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing data source passwords in workbook ?
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. .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing data source passwords in workbook ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Source Data in a Formula | Excel Discussion (Misc queries) | |||
Changing Data Source | Excel Worksheet Functions | |||
Changing data source in a pivot | Excel Discussion (Misc queries) | |||
Changing query data source | Excel Discussion (Misc queries) | |||
Changing Data Source Driver | Excel Programming |