Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 03-03-2005, 08:28 AM   #1
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
Update bit Field in Access using SQL linked Table

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

manojab is offline   Reply With Quote
Old 03-03-2005, 03:25 PM   #2
Louie
Registered User
 
Join Date: Feb 2005
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Louie is on a distinguished road
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?
Louie is offline   Reply With Quote
Old 03-04-2005, 08:48 AM   #3
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
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

manojab is offline   Reply With Quote
Old 03-04-2005, 09:13 AM   #4
Louie
Registered User
 
Join Date: Feb 2005
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Louie is on a distinguished road
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"
Louie is offline   Reply With Quote
Old 03-04-2005, 09:27 AM   #5
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
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 is offline   Reply With Quote
Old 03-04-2005, 09:27 AM   #6
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
also my data type in SQL2000 is a bit
manojab is offline   Reply With Quote
Old 03-04-2005, 09:32 AM   #7
Louie
Registered User
 
Join Date: Feb 2005
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Louie is on a distinguished road
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?

Louie is offline   Reply With Quote
Old 03-04-2005, 09:38 AM   #8
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
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
manojab is offline   Reply With Quote
Old 03-04-2005, 09:45 AM   #9
Epic
Newly Registered User
 
Join Date: Dec 2004
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Epic is on a distinguished road
You may check the followings:
Epic is offline   Reply With Quote
Old 03-04-2005, 09:58 AM   #10
Epic
Newly Registered User
 
Join Date: Dec 2004
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Epic is on a distinguished road
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.
Epic is offline   Reply With Quote
Old 03-04-2005, 10:58 AM   #11
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
thanx Epic
There is no bit field with Null Value.. & also i have the default value saved as 0
manojab is offline   Reply With Quote
Old 03-07-2005, 08:49 AM   #12
manojab
Registered User
 
Join Date: Feb 2005
Location: Mobile, AL
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
manojab is on a distinguished road
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!!!

manojab is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 08:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World