ODBC Call Failed from Access 2003 Query

Posted on 2007-10-18
Last Modified: 2013-12-17
XPSP2 w Office 2K3.  Access 2003 Front End, SQL 2000 SP4 Back End.  I have to know why my fix worked here.  I had a trial balance report that worked fine until yesterday we started getting ODBC call fails.  I isolated the query that ran and the table and column causing the problem.  In fact if I just elimniated the criteria on the column it ran fine but returned the wrong dataset.  The field is a 'bit' field and the criteria was 'True'.  This is working in all kinds of other queries, tens of which are in the GUI, using the True/False criteria.  The table is nested #4 referentially left to right.  It still is.  

If I simply change the criteria from True to - get this - >0!  SQL stores 1 or 0, Access shows -1 or zero respectively, so I get the math but when did the True/False thing stop working?  

I have deleted the table from the query re-compacted, deleted the linked table to SQL and relinked and re-added the table to the query.  Same result.  

NOTE:  If I make a separate query with just that table and the True/False criteria reference, it works fine!  Also, I make back up copies of the .mdb file everyday during development as revisions warrant it.  The query worked fine in the back up .mdb but when imported into the current .mdb as an imported object it fails.  Any guesses?
Question by:jdfuller

    Author Comment

    I just read my question and it looks like words are missing.  I think my head was going faster than my hands.  Sorry if it is confusing.  Maybe it dropped some words when I added the hard returns but I think you get the message.  :o)  THis answer should be worth at least 1,000 points but I couldn't assign that many.
    LVL 3

    Accepted Solution

    When using any bit field I noticed that Access and SQL server have issues when  comparing 1 and -1 so I  always set my test criteria as True (<> 0) and False (=0).  I Think the problem is that Access sees true as 1 and sql stores it as -1.  So when the code in access converts the true to bit it converts to 1 and when comparing it to -1 it thinks ther is no match or errors out.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    Viewers will learn how to use the Hootsuite Dashboard.
    The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now