Post reply

ODBC Connection Fail

  • March 2, 2006 at 11:29 am

    #96670

    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.

  • March 3, 2006 at 1:01 pm

    #624717

    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

      

  • March 4, 2006 at 3:05 am

    #624771

    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://qa.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