Datatype conversions from SQL to Access

[Baffee]

I use SQL tables to store all my access data and then link
to Access 2000 with and ODBC connection. I am looking for
a datatype in SQL that will convert to Yes/No in Access. I
will be storing a yes/no or 0/1 to indicate whether a
process has been completed.

I have tried datatype Bit and it converts to the Yes/No
Datatype in Access but then you can't update anything else
in the record. When you try to make any changes to the
Access table you get a box with the message:
"The record has been changed by another user since you
started editing it. If you save the record your will
overwrite the changes the other user made."

There is an button Save Record at the bottom of the box,
but it is grayed out and can't be chosen.

Any ideas how I can get work around this problem?

Thanks!

 

[Roger Carlson]

See the following MS Knowledgebase article to fix this problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q278696

 

[Joe Fallon]

Probably because when you defined Bit you allowed Nulls so it is really a 3
way data type.
Just uncheck Allow Nulls so it is 2 way (Y/N, T/F, 1/0).

 

[Sylvain Lafontaine]

It is also often easier to use the TiniInt instead of the Bit type because
the later will often be equivalent to 0/-1 instead of 0/1, for historical
reason.

Bit type must also be converted to Int if you want to perform numerical
operations on them, like the aggregate function Sum(); which lead to more
complicated instructions.

S. L.