none
ODBC-Call Failed - Access RRS feed

  • Question

  • We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.

    1. It not happens always, mosty we're fine.
    2. When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.

    We would be very interested in someone with a solution to this problem.

    Friday, September 15, 2006 9:03 AM

Answers

All replies

  • What ODBC driver and version are you using and to what version of SQL Server are you connecting?

    You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.

    Vaughn

    Monday, September 18, 2006 6:41 PM
  • We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?
    Tuesday, October 3, 2006 3:03 PM
  • Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access.  I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution. 

    http://support.microsoft.com/kb/227413/

    I see you're using Access 2003 so this isn't likely the cause.  Have you tried ODBC tracing through the ODBC Data Source Administrator control panel?  Correlating that log to when the failure occurs might provide more clues as to the cause.

    Vaughn

    Tuesday, October 3, 2006 5:26 PM
  • The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.
    Tuesday, November 14, 2006 3:59 PM
  • So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.

    Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).

    Wednesday, November 15, 2006 7:39 AM
  • I found some information that may shed some light on the differences at:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp

    Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.

    A Dynaset (Inconsistent) allows you to update all data fields.

    Tuesday, November 21, 2006 10:05 AM
    Moderator
  • I had the sam problem after transition from access to sql server. only 5 out of 120 tables had odbc error after linking. My problem was that  column names in access were composed by two words, like "AB COL". after i renamed column name in sql in ABCOL it was OK. btw every "errorish" column name in sql is in brackets []. Hope this helps...

    Saturday, January 5, 2008 11:55 AM
  • Re-Link your tables.
    Friday, June 20, 2008 8:38 PM
  • I had a similiar problem, and I was able to solve it by increasing the ODBC timeout from 60 to 180. This can be done by opening the access SQL-Pass-Through query in Design mode and right clicking on the window then accessing the properties. One of the properties is ODBC timeout.
    • Proposed as answer by Psalm3_3 Wednesday, September 21, 2011 5:11 PM
    Monday, October 20, 2008 11:41 PM
  • I have exactly the same problem with Access linking to MySQL.  I'll try to change the ODBC timeout from 60 to 180 or some bigger number and see what happens.  Who would've thought of it?  Thanks a bunch for the tip!

    Well, it does not work for me.  The program always resets the number back to 60, and it fails with the same error message every time.
    Any more ideas out there? 

    Thanks!

    Saturday, October 17, 2009 12:50 AM
  • What I did that worked for me was to open the Access Query Properties window, change the timeout value (I used 180), tab to the next field, close the Properties window, and Save the query.  This is all behavior within Access, so I would think it would be the same regardless of the backend database you're connecting to (I'm connecting to SQL Server).  The one thought that comes up is to wonder whether you are in a situation where you can't save changes to the Access queries?  If so that would explain the behavior.
    Wednesday, December 9, 2009 3:54 PM
  • I received this error after mistakenly changing my primary key field data type from numeric to int in a sql server table. The problem resolved when I changed it back to numeric field. In my case I was working with an Access database that was linked to a SQL Server table.

    Friday, January 29, 2010 5:09 PM
  • in my case it was becase ms access with sql server driver from odbc  can't handle double quots (") in column names, it does not use the squere barakets as access does but rather usese the ansi double quotes to enclose column names, and it fails to use escape codes like \"

    so i deleted the double quotes from column names and it worked

    somthing "name":  >> somthing name: 

    Sunday, April 18, 2010 7:56 PM
  • I'm also getting this error with Oracle and Access 2007. Check detailed description in my thread. Please help!

    ODBC --Call Failed.

    [Oracle][ODBC][Ora]ORA-01013: User Requested cancel of current operation (#1013)

    Tuesday, June 29, 2010 10:19 AM
  • I know this is an old thread but I had this same problem and hopefully my solution will help someone.

    I had this issue using Access 2007 with a MSSQL 2005 backend. The issue occured because the query I was trying to run was incorrect.

    First, get the source SQL code of the problematic query and run it in your database's management console, and check if your syntax is correct and the query results are okay.  I removed a column in one of my tables and I got this error when I referenced that column, which no longer existed.

    Also make sure you refresh the links for your linked-database tables in Access if you are using such tables and have queries that rely on them.

    Cheers

    Wednesday, March 9, 2011 3:46 PM
  • same issue, access 2003 with MSSQL 2005 backend. I later realised a table is missing from the link in the query, adding this table back to the query fixed this issue.
    Monday, June 20, 2011 2:27 AM
  • Here are some of the errors I was getting:

     

     

    EXECUTE ERROR: ODBC--call failed.
    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    EXECUTE ERROR: The Microsoft Jet database engine cannot find the input table or query 'qryTestQuery'.  Make sure it exists and that its name is spelled correctly.
    


     

     

    Please note that I set the following on my command object, and it solved the problem:

     

     

    Jet OLEDB:ODBC Command Timeout=180

     

     

    Thanks for the tips!

     

    All the best,

     

    Roger



    • Edited by Psalm3_3 Friday, September 23, 2011 6:27 PM
    Wednesday, September 21, 2011 5:14 PM
  • Relinking tables typically helps this issue.  One suggestion, if you in a multi-developer environment is to check around and see if anyone is running an extremely complex query that has gotten hung up.  (I know this is an extremely old post, but these are the ones that turn out to be the most helpful)
    • Proposed as answer by New_Ibro Tuesday, October 9, 2012 5:20 PM
    • Unproposed as answer by New_Ibro Tuesday, October 9, 2012 5:20 PM
    • Proposed as answer by kair2 Monday, June 9, 2014 10:02 AM
    Tuesday, July 24, 2012 1:45 PM
  • After spending hours looking into this error...it was as simple as removing a space in one of my columns.  Excel doesn't seem to have an issue querying a view in SQL but Access decided it didn't care for that space. If it was going to handle the data...the space had to go.

    Even after trying all of the 'time out' settings and configurations and even refreshing the connection and playing with Dynasets...it was just a space. *sigh

    oh and before anyone points this out...it DID return the data intermittently...odd but true.

    Wednesday, August 12, 2015 2:57 PM