?
Solved

ODBC Call Failed from Access 2003 Query

Posted on 2007-10-18
2
Medium Priority
?
2,206 Views
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?
0
Comment
Question by:jdfuller
2 Comments
 

Author Comment

by:jdfuller
ID: 20105404
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.
0
 
LVL 3

Accepted Solution

by:
gemost earned 1500 total points
ID: 20112440
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.

HTH
gemost
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Web hosting control panels were first developed to make it faster and easier for most users to set up and operate websites. The graphical user interface (GUI) allows users to perform tasks by pointing and clicking rather than typing highly specific…
Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

862 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