ODBC Connection Fail
-
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.
-
Tobias:
While working in a Finance department I was able use Excel pivot tables fairly easily when the datasource was ACCESS or SQL Server. You said that perhaps 'Excel cannot pull third source data through the Access layer." If this is the case have you tried using using passthrough queries in ACCESS to your SQL Server? I don't kow that that will work for you but something I would probaly try if I thought Access or the jet engine was gettign in the way.
Good luck,
Ross
-
Plz don't cross post - we get all messages in all forums in the daily email... (or at least include links to your other posts so all answers are in the one thread)..
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=262845
Thanx!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply