Group:  Microsoft Excel ยป microsoft.public.excel.querydao
Thread: How to edit SQL connection?

Geek News

How to edit SQL connection?
"Michael MacGregor" <nospam[ at ]nospam.com> 9/20/2006 12:57:20 PM
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 9/21/2006 10:25:02 AM
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> 9/21/2006 1:48:04 PM
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


Home | Search | Terms | Imprint Contact
Newsgroups Reader - provided by WiredBox.Net