How to edit SQL connection? "Michael MacGregor" <nospam[ at ]nospam.com> 20.09.2006 12:57:20 We have an Excel spreadsheet that was created by someone who left the company and who did not document what they had done. This spreadsheet is supposed to be connect to a SQL Server to extract data for a report, however the server on which the data resides was changed, by the same person who created the spreadshseet and who subsequently forgot to update the spreadsheet, and this is probably only the first of many that will surface over time. After it opens and attempts to refresh, it causes an error: "Connection failed: SQLState: '08004' SQL Server Error: 4060 Server rejected the connetion; Access to selected database has been denied" Clicking on "OK" results in a SQL Server Login dialog box appearing. In the Server drop-down list it has the name of the server on which the data used to reside, so it's hardly surprising that access was denied because the database doesn't even exist there. It also has Use Trusted Connection checked. Now how do I edit the connection parameters so that it automatically connects to the right server rather than raise an error and then prompt for the connection details? I can't figure out where this is defined. Thanks. Michael MacGregor Database Architect (not an Excel expert at all!)

RE: How to edit SQL connection? ricol 21.09.2006 10:25:02 Two object exist for picking data from SQL-S to Excel, ie pivottable and querytable. The connection property is reachable by code in VBA with something like: ?Activesheet.querytables(1).connection ?activesheet.pivottables(1).pivotcache.connection in the debug window reached in VBA by Alt/F11 in Excel if you clcik on the sheet containing the data. The connection property could also be changed in the Excel interface: If a querytable right/click in the table and select Edit Query to enter yhe wizard for building the query. If a pivottable right/click and select wizard. All connections are based on ODBC so to get it reachable for many users could be a bit tricky since the ODBC-connection is either a file or an entry in the registry in the user's machine.... Good Luck -- Rickard Olsson, Sweden www.ricol.se/ricoleng "Michael MacGregor" wrote: [Quoted Text] > We have an Excel spreadsheet that was created by someone who left the > company and who did not document what they had done. This spreadsheet is > supposed to be connect to a SQL Server to extract data for a report, however > the server on which the data resides was changed, by the same person who > created the spreadshseet and who subsequently forgot to update the > spreadsheet, and this is probably only the first of many that will surface > over time. > > After it opens and attempts to refresh, it causes an error: > > "Connection failed: > SQLState: '08004' > SQL Server Error: 4060 > Server rejected the connetion; Access to selected database has been denied" > > Clicking on "OK" results in a SQL Server Login dialog box appearing. In the > Server drop-down list it has the name of the server on which the data used > to reside, so it's hardly surprising that access was denied because the > database doesn't even exist there. > > It also has Use Trusted Connection checked. > > Now how do I edit the connection parameters so that it automatically > connects to the right server rather than raise an error and then prompt for > the connection details? I can't figure out where this is defined. > > Thanks. > > Michael MacGregor > Database Architect (not an Excel expert at all!) > > >

Re: How to edit SQL connection? "Michael MacGregor" <nospam[ at ]nospam.com> 21.09.2006 13:48:04 Ok, I know absolutely nothing about VBA, so I'm afraid I'm still lost as to where to find this information. I did Alt-F11 but where then do I go to find the connection parameters, if there are any? I tried using the Edit Query, but I can't figure out how to get to it. It looks like it might be PivotTable because when I right-click in a data cell, the context menu lists PivotTable Wizard, but how do I get to the connection properties using that? This is a networked file, I don't have any suitable ODBC connections defined on my PC but I can alter the connection properties when the dialog window is displayed and it will connect. I find it very difficult to get to this information, couldn't MS have designed this better. Thanks for your response. Michael MacGregor Database Architect