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.
Please e-mail me/ respond if you have additional questions for resolving
this. I am prepared to offer a graphic representation of this if necessary.
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.
Please e-mail me/ respond if you have additional questions for resolving
this. I am prepared to offer a graphic representation of this if necessary.
