Update bit Field in Access using SQL linked Table (1 Viewer)

manojab · Mar 3, 2005

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

Louie · Mar 3, 2005

Bit fields in SQL server are either true (-1) or False (0). Your Update would work for either of those values but not 1. Why do you want it to be 1?

manojab · Mar 4, 2005

Bit Fields are true (-1) in Access but in SQL server i think its 1..but even if it is -1 it still does not work?.. the data translation from Acess to SQL 2000 is not happening!!

thanx
M

Louie · Mar 4, 2005

I set up a test and it seems to work fine here. Did you use SqlServer Enterprise Manager to look at the results of the update? Enterprise Manager should show -1 in its display.

"Update tblTest set process = true where process = false"

manojab · Mar 4, 2005

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

manojab · Mar 4, 2005

also my data type in SQL2000 is a bit

Louie · Mar 4, 2005

Are you saying that the update:

"Update tblTest set process = -1 where process = 0"

Does not set process which is defined as type bit in the SqlServer table?

Or are you saying you must use 1 in your application?

manojab · Mar 4, 2005

Update tblTest set process = 0 where process = 1

or if try

Update tblTest set process = 0 where process = -1

both of these queries fail the SQL datatype is a bit

thanx
M

Epic · Mar 4, 2005

You may check the followings:

Epic · Mar 4, 2005

Sorry

Is there any "bit" field in SQL db with <null> value? Not 0 or -1 ; True or false!
Use the enterprise manager for this!

Have you set the default value for the bit to be 0? - this is to avoid errors in the future.

Then should work. Try to create a normal update query first and if works then copy the sql in vba...

do not try to update the <null> values with other update query using condition... save your table in a spredsheet then set all values to 0 or -1 and correct them. I had similar problem and that was the solution.

Bye.

manojab · Mar 4, 2005

thanx Epic
There is no bit field with Null Value.. & also i have the default value saved as 0

manojab · Mar 7, 2005

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!!!

Update bit Field in Access using SQL linked Table (1 Viewer)

manojab

Registered User.

Louie

Registered User.

manojab

Registered User.

Louie

Registered User.

manojab

Registered User.

manojab

Registered User.

Louie

Registered User.

manojab

Registered User.

Epic

Registered User.

Epic

Registered User.

manojab

Registered User.

manojab

Registered User.

Similar threads

Users who are viewing this thread