WraithSystems
Programmer
Greets.
This may or not be a good forum group to start in, if not, apologies.
Microsoft had no solution for this:
I have an Excel File, a Access Query, and a SQL Based Database. The
relation? The Access Query has both native and polled/linked tables to the
SQL native backend. A Query is designed in Access (for ease) to poll the SQL
side through the linked tables, and a couple of other crosstab queries. This
Access Query needs to be accessed from Excel Pivot Table Manager as a link to
an outside source.
The solution... a user can refresh their pivot table using ONLY Excel
collecting data from both Access AND SQL.
The method... use a Excel Pivot Table Manager.
The problem... "ODBC connection failed" to the SQL backend.
Hypothesis... Excel cannot pull third source data through the Access layer.
ODBC Connections have been verified that they exist and function for both
Access and the SQL side on the PC attempting this feat.
Environment: Dozens of queries have been designed in access as a primary
mode of data extraction from the SQL side. In fact, the primary interface to
the data is actually programmed through VB in Access. (Not my fault). The
problem is that we have layers of experienced users, primarily the ones
responsible for queries are only capable of designing them through Access.
The final End User for the Pivot tables is not allowed access to the queries,
for stability concerns, and lack of End Users experience in anything
complicated.
It is vital that the interaction for the End User is near zero to obtain the
data, and I have to work with the query writing person to pull things into
access.
Microsoft support has not been able to either fully understand or workaround
this issue.
It has been offered as a solution to create a pass through query, however:
This is the "Master" Select Statement that I need to run in the Query editor, Most of this accesses the SQL backend:
SELECT tblInItem.ItemId, tblInItem.Descr, tblInItemAddlDescr.AddlDescr, [IN - On Hand Qty].onhand
FROM (tblInItem LEFT JOIN tblInItemAddlDescr ON tblInItem.ItemId = tblInItemAddlDescr.ItemId) LEFT JOIN [IN - On Hand Qty] ON tblInItem.ItemId = [IN - On Hand Qty].ItemId
WHERE (((tblInItem.ItemStatus)=1) AND ((tblInItem.SalesCat)="PR") AND ((tblInItem.UsrFld1)="yes"))
ORDER BY tblInItem.ItemId;
Notice that the " [IN - On Hand Qty].onhand " is actually a reference to another query in ACCESS:
SELECT Sum([qty]-[invoicedqty]-[removeqty]) AS onhand, tblInQtyOnHand.ItemId
FROM tblInQtyOnHand
GROUP BY tblInQtyOnHand.ItemId
Two issues exist here. First, since I am loading the ODBC driver for the SQL backend, the [IN - On Hand Qty].onhand is fouling up due to the fact it in is an ACCESS table.
Secondly, although I could theoretically combine the two, (I am not quite sure yet how to nest select statements) I would have to do this for ALL the items I am importing into Excel, some of which have SEVERAL ties to Access tables that I would have to nest into the main SQL statement.
Sure it is job security that I would become a doggon expert at writing nested SQL statements, and I wouldn't mind doing it if I didn't have a network to run as well, there HAS to be another alternative like a nested odbc connection that can take tables from two completely different sources.
Please help obi-wan, you're our only hope!!!
This may or not be a good forum group to start in, if not, apologies.
Microsoft had no solution for this:
I have an Excel File, a Access Query, and a SQL Based Database. The
relation? The Access Query has both native and polled/linked tables to the
SQL native backend. A Query is designed in Access (for ease) to poll the SQL
side through the linked tables, and a couple of other crosstab queries. This
Access Query needs to be accessed from Excel Pivot Table Manager as a link to
an outside source.
The solution... a user can refresh their pivot table using ONLY Excel
collecting data from both Access AND SQL.
The method... use a Excel Pivot Table Manager.
The problem... "ODBC connection failed" to the SQL backend.
Hypothesis... Excel cannot pull third source data through the Access layer.
ODBC Connections have been verified that they exist and function for both
Access and the SQL side on the PC attempting this feat.
Environment: Dozens of queries have been designed in access as a primary
mode of data extraction from the SQL side. In fact, the primary interface to
the data is actually programmed through VB in Access. (Not my fault). The
problem is that we have layers of experienced users, primarily the ones
responsible for queries are only capable of designing them through Access.
The final End User for the Pivot tables is not allowed access to the queries,
for stability concerns, and lack of End Users experience in anything
complicated.
It is vital that the interaction for the End User is near zero to obtain the
data, and I have to work with the query writing person to pull things into
access.
Microsoft support has not been able to either fully understand or workaround
this issue.
It has been offered as a solution to create a pass through query, however:
This is the "Master" Select Statement that I need to run in the Query editor, Most of this accesses the SQL backend:
SELECT tblInItem.ItemId, tblInItem.Descr, tblInItemAddlDescr.AddlDescr, [IN - On Hand Qty].onhand
FROM (tblInItem LEFT JOIN tblInItemAddlDescr ON tblInItem.ItemId = tblInItemAddlDescr.ItemId) LEFT JOIN [IN - On Hand Qty] ON tblInItem.ItemId = [IN - On Hand Qty].ItemId
WHERE (((tblInItem.ItemStatus)=1) AND ((tblInItem.SalesCat)="PR") AND ((tblInItem.UsrFld1)="yes"))
ORDER BY tblInItem.ItemId;
Notice that the " [IN - On Hand Qty].onhand " is actually a reference to another query in ACCESS:
SELECT Sum([qty]-[invoicedqty]-[removeqty]) AS onhand, tblInQtyOnHand.ItemId
FROM tblInQtyOnHand
GROUP BY tblInQtyOnHand.ItemId
Two issues exist here. First, since I am loading the ODBC driver for the SQL backend, the [IN - On Hand Qty].onhand is fouling up due to the fact it in is an ACCESS table.
Secondly, although I could theoretically combine the two, (I am not quite sure yet how to nest select statements) I would have to do this for ALL the items I am importing into Excel, some of which have SEVERAL ties to Access tables that I would have to nest into the main SQL statement.
Sure it is job security that I would become a doggon expert at writing nested SQL statements, and I wouldn't mind doing it if I didn't have a network to run as well, there HAS to be another alternative like a nested odbc connection that can take tables from two completely different sources.
Please help obi-wan, you're our only hope!!!