Datatype BIT problem with Access front-end AND SQL back-end

[Tommy23]

This is the first time i post something on this forum, so i'm not sure if
this tread is on the right place.

I am having trouble with a BIT field when migrating an Access database to an
SQL database.

I will try to explain the situation:

We were working with an Access database combined with Access forms.
Because the database was getting to big en everything started to work very
slow i decided to migrate the access database to microsoft SQL server.

On first sight everything worked fine en fast but i discovered a problem with
the conversion from a yes/no table in access to a BIT table in sql server.
Problem is that Access and SQL shows a '0' value in the table, but actually
the '0' is a null in access. So a blanc field.
I tried to give a default value to the table in SQL and tried to give the 0
in the access form code, But everytime i get the message 'type mismatch'.

I am working on this problem for a few days now and tried everything i could
find on the internet but nothing resolved the problem.

Maybe someone here can give me solution?

Thanks in advance.

Tommy

 

[MichaelRay via AccessMonster.com]

Access does not work well with SQL bit fields. The best solution is to change
the bit field in the SQL table to a small int. It will then hold -1 as true
and 0 as false.

 

[Paul Shapiro]

You have to set the SQL Server bit field to not allow nulls or Access has
problems. It's up to you whether or not you want to assign a default value.

When you move the data, you'll need to decide whether to replace the Access
null with False or True. Whichever you pick, you can either:
a) Use a query to move the data to SQL Server and replace nulls with your
chosen value in the sql.
b) Update the existing Access table to replace nulls with your chosen value.

SQL Server and Access differ in their boolean representation. Both use a
value of 0 for false, but Access uses -1 for true while SQL Server uses +1.
For sql to work under both conditions, you can test for bitColumn=0 or
bitColumn<>0, rather than using true and false.
Paul Shapiro