SSMA - Issue with Boolean Access Fields

[Minty]

I'm sure this has been mentioned before but I thought it had been addressed, or we had a workaround.
When importing data using SSMA that includes Boolean (Yes/No) fields, if you accepted the default mapping of Byte datatype then SSMA swapped all your lovely -1 data to +1's. Which meant that if you had any code checking for -1 (instead of <>0 ) it failed.

To ensure this doesn't affect legacy systems we always map Boolean fields to SmallInt datatypes, which can handle the negative value.
Only they don't appear to be converted correctly anymore. Or something else has changed...

In a recent import all the Boolean fields have been imported into a small int field as 1 or 0. Rendering hundreds of lines of legacy code useless, apart from when they are updated from Access which will chuck a -1 back where it should be.

I have tried submitting a bug report to the SSMA team but it was reject as spam!

The info from the bug report was:
Tool version: SSMA for Access 10.0.24283.1001
OS version: Microsoft Windows NT 10.0.22621.0
Correlation: 576223c1-a09b-40cb-92b5-7c3d794cd581

Anyone else experienced this, and have a simple solution?

 

[AccessBlaster]

Have you tried BIT vs INT?
I believe that's what I had to do when I was upsizing my tables to SQL.

Edit: never mind I see you used byte datatype, I was confused for a sec.

 

[GPGeorge]

I'm sure this has been mentioned before but I thought it had been addressed, or we had a workaround.
When importing data using SSMA that includes Boolean (Yes/No) fields, if you accepted the default mapping of Byte datatype then SSMA swapped all your lovely -1 data to +1's. Which meant that if you had any code checking for -1 (instead of <>0 ) it failed.

To ensure this doesn't affect legacy systems we always map Boolean fields to SmallInt datatypes, which can handle the negative value.
Only they don't appear to be converted correctly anymore. Or something else has changed...

In a recent import all the Boolean fields have been imported into a small int field as 1 or 0. Rendering hundreds of lines of legacy code useless, apart from when they are updated from Access which will chuck a -1 back where it should be.

I have tried submitting a bug report to the SSMA team but it was reject as spam!

The info from the bug report was:
Tool version: SSMA for Access 10.0.24283.1001
OS version: Microsoft Windows NT 10.0.22621.0
Correlation: 576223c1-a09b-40cb-92b5-7c3d794cd581

Anyone else experienced this, and have a simple solution?

In reality, Access is about the only place where you find True = -1

In most other software languages, and in SQL Server datatypes, 0 = False and 1 = True. VBA is virtually unique in using 0 = False and -1 = True, so there's that to consider.


The reason VBA uses -1 for true is derived from the way it's encoded in binary, and that's a bit beyond my ability to explain it, but there are excellent videos explaining that and a lot more here:

And here:

We're still working on the other videos planned for this series; I hope to be able to complete the editing on the next one soon.

None of that addresses your current dilemma, of course. However, I do think it's helpful to understand what lies behind the datatypes False and True in Access.

 

[DickyP]

In reality, Access is about the only place where you find True = -1

In most other software languages, and in SQL Server datatypes, 0 = False and 1 = True. VBA is virtually unique in using 0 = False and -1 = True, so there's that to consider.


The reason VBA uses -1 for true is derived from the way it's encoded in binary, and that's a bit beyond my ability to explain it, but there are excellent videos explaining that and a lot more here:

Just not true -1 (Byte value Hex FF) , with 0 Byte value Hex 00) had been the standard since the 1970s when single signed bytes were used to represent Boolean in systems, which had no intrinsic Boolean data types. Access just followed the norm when it was being created in the late 1980s, and VB /VBA continued accepted practice in the various basics it was developed from.

It was certainly the norm in Pascal when I was learning it and in Delphi to this day. Was the same in Modular 2 and Ada back in the day.

These days, I agree, any non-zero value is treated as True in most systems (including Access and VBA in code). How SQL Server physically stores True I can't comment on, but last time I used Oracle and DB2 Boolean was stored as -1 for true.

 

[Josef P.]

A side note: If nobody cared about the numeric value of True in Access queries and simply wrote True, queries on SQL Server tables linked via ODBC would also work.

Select ... From LinkedServerTable where BitDataField = True

This ensures that a Boolean/Bit data type is transmitted instead of a number (-1).

 

[GPGeorge]

Just not true -1 (Byte value Hex FF) , with 0 Byte value Hex 00) had been the standard since the 1970s when single signed bytes were used to represent Boolean in systems, which had no intrinsic Boolean data types. Access just followed the norm when it was being created in the late 1980s, and VB /VBA continued accepted practice in the various basics it was developed from.

It was certainly the norm in Pascal when I was learning it and in Delphi to this day. Was the same in Modular 2 and Ada back in the day.

These days, I agree, any non-zero value is treated as True in most systems (including Access and VBA in code). How SQL Server physically stores True I can't comment on, but last time I used Oracle and DB2 Boolean was stored as -1 for true.

Historically speaking, yes, that was true.

 

[isladogs]

@Minty
For info, the Access team are already aware of the issue
I also covered several related points in this article:

Sort Boolean Fields in Linked SQL Server Tables

This article discusses why sorting linked SQL Server bit fields gives the exact opposite of the expected results compared to sorting boolean fields in local / linked Access tables. The results can be confusing to your database users. The article also gives several workarounds to prevent this...

www.isladogs.co.uk