gsaundersbranch
New Member
- Joined
- Jun 27, 2016
- Messages
- 3
Using Excel 2013 and SQL 2012: Testing SQL Server (OLEDB) and SQL Server (ODBC) using Microsoft Query
ISSUE 1:
In development environments I have used we just create a single connection and many queries can run from that connection.
However in Excel it seems you have to create a new connection for every query that is different.
Is there not a way to have a single connection where each query uses the connection, but it has it's own query string?
If you are note using trusted connection and you don't want to save the password it appears to ask you for every single query as they each have their own connection.
Is this the way it supposed to be Excel?
ISSUE 2:
All of our queries need to re-run as the user selects a different job number. So let's assume each query I create will have it's own connection. Is there a way though VBA to update the query (created manually initially) so the where clause can change (like parameters) as the user selects a new job. Basically when a cell changes or when they click a button.
This also ties back to best way of creating and running queries with parameters (or needed different where clause). If you use Microsoft Query (ODBC) you can do this by using the question mark as parameters and assigning them to cells.
BUT if this is problematic (as it seems to be for us) it would stand that the SQL Server connection type (OLEDB) would be the next option, but it doesn't allow for the question mark parameters which means it probably requires VBA.
ISSUE 3:
We did try the Microsoft Query Method and tied the parameter to a pivot table where the results we needed were always in the first row. This worked great for the first 2 ODBC queries, but as soon as we add a 3rd query excel will completely crash and bomb and corrupt the file. It should not be a limitation of the number of queries as I have seen spreadsheets with a lot more queries and it isn't the amount of data as it is all small. My only quess is it may have to do with them all being parameterized. Why? Well if I replace the ? with actual values in the query they run flawlessly and everything saves just fine. Only when I have the 3rd query using parameters. I thought maybe it was because the cell values for the parameters were coming from a pivot table that was linked to SSAS, but I then just tried going to normal cells for the values and get the same results. A complete crash when adding the third connection.
Relatively new to multi-query and parameters in Excel, but it seems to be somewhat fussy.
Thanks in advance for any and all feedback.
Greg
ISSUE 1:
In development environments I have used we just create a single connection and many queries can run from that connection.
However in Excel it seems you have to create a new connection for every query that is different.
Is there not a way to have a single connection where each query uses the connection, but it has it's own query string?
If you are note using trusted connection and you don't want to save the password it appears to ask you for every single query as they each have their own connection.
Is this the way it supposed to be Excel?
ISSUE 2:
All of our queries need to re-run as the user selects a different job number. So let's assume each query I create will have it's own connection. Is there a way though VBA to update the query (created manually initially) so the where clause can change (like parameters) as the user selects a new job. Basically when a cell changes or when they click a button.
This also ties back to best way of creating and running queries with parameters (or needed different where clause). If you use Microsoft Query (ODBC) you can do this by using the question mark as parameters and assigning them to cells.
BUT if this is problematic (as it seems to be for us) it would stand that the SQL Server connection type (OLEDB) would be the next option, but it doesn't allow for the question mark parameters which means it probably requires VBA.
ISSUE 3:
We did try the Microsoft Query Method and tied the parameter to a pivot table where the results we needed were always in the first row. This worked great for the first 2 ODBC queries, but as soon as we add a 3rd query excel will completely crash and bomb and corrupt the file. It should not be a limitation of the number of queries as I have seen spreadsheets with a lot more queries and it isn't the amount of data as it is all small. My only quess is it may have to do with them all being parameterized. Why? Well if I replace the ? with actual values in the query they run flawlessly and everything saves just fine. Only when I have the 3rd query using parameters. I thought maybe it was because the cell values for the parameters were coming from a pivot table that was linked to SSAS, but I then just tried going to normal cells for the values and get the same results. A complete crash when adding the third connection.
Relatively new to multi-query and parameters in Excel, but it seems to be somewhat fussy.
Thanks in advance for any and all feedback.
Greg
