Update bit Field in Access using SQL linked Tables

[manojab]

Hi Guys, I need some help to understand this...

I have an application in Access 2000(front end) & I use linked tables from SQL 2000(Back end). I have a table test.. I'm tryin to update a bit field from 0 to 1 using an update query. It does not perform the update.

"Update tblTest set process = 1 where process = 0"
this is a query in access
can you please help me...what am i missing?

thanx
M

 

[cmmrfrds]

Bit fields are a problem going through ODBC from SQL Server, at least that was true last year. The ODBC driver I used did not map bit fields correctly. If this is still the case, then maybe the DBA could change the fields to tinyint, or use an ADO connection through OLEDB to do the updates.

 

[manojab]

thanx for the reply.. but when i try the same query with Access 2000 front end and sql 7.0 back end..it updates the field with no problems!!!
thats what i don't understand!!!

 

[PHV]

Provided you don't use a passthru query, have you tried this ?
Update tblTest set process = True where process = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[manojab]

Yea it works when i use true or false in the query but in all my applications I use 1 and 0 or -1 and 0 for true or false. coz SQL7.0 translates the boolean just fine... but SQL 2000 fails to do the translation
So if i start using true or false.. i will have to change all the update queries in all the applications..which would be a killer

 

[cmmrfrds]

Here are some theards that might give some insight to current and future problems with the bit data type through ODBC.

thread700-571093
thread700-827171
thread183-733937

 

[manojab]

Here's what i found out....if you change the compatability options under SQL 2000 to SQL Server70 instead of 80 then it works fine....

to get there right click on a data base, then properties, then options & under that u will see a drop down called Compatability Level

hope this helps if anyone else has a problem!!

thanx for all ur help guys!!!