Boolean - Access Linked Tables to SQL Server - Data Types

[Rx_]

About to set up another Linked Table column in MSSQLServer for an MSAccess type Boolean. MSAccess 2010 - SQL Server 2008 - ODBC: SQL Server Native Cleint 11.

Revisiting old ideas to make sure they are current. Here are my thoughts, others are very invited to validate or provide alternative suggestions.

In Access yes/No Data types corresponds to the SQL Server BIT data type.
In Access TRUE is -1 and FALSE is 0.
In SQL Server, TRUE is 1 and FALSE is 0.

Access, when linked to this data type will display NULL as FALSE.
To avoid the NULL, set the SQL Server DEFAULT value to 0.

In Access Data view - the linked table field (default) displays as Zero.
In Access Linked Table Design view - the field data type shows as Yes/No - below the format can be changed to True/False.

This allows a query in MSAccess (using the Linked Table) to use a True / False on the field:
SELECT Wells_Status1.ID_WellStatus1, Wells_Status1.Status1, Wells_Status1.Permitted
FROM Wells_Status1
WHERE (((Wells_Status1.Permitted)=True));

 

[Ranman256]

We tried this, but sometimes, SQL svr would error out on BIT fields.
It really hated BIT fields.

 

[pbaldy]

Interesting, as I've used the setup Rx mentioned for years (Bit, no Nulls, 0 default value). What was the nature of the errors?

 

[Rx_]

Good, I was looking forward to any discussion.
B.T.W. if the default is not set to No Null (talk about double negatives!) then an article talked about issues depending on the VBA code using null vs a number when logic was used.
Just wodering if that might have contributed.

Soon, my relinking code will destroy all DSN-Less connections and then re-connect them via vba code against a table with the Linked Table names.
I expect my display as True / False to be undone so it will show 1 or 0 again.
Wondering if I need to add code to set that Linked Well property.

 

[Ranman256]

I think it errored ONLY IF, a field was changed to BIT that wasnt originally. (formly,integer)
But we lived fine with integer.

 

[BlueIshDan]

Access, when linked to this data type will display NULL as FALSE.
To avoid the NULL, set the SQL Server DEFAULT value to 0.

Would this not change true values to false when -1 is not recognized?
This question is more for my education lol

 

[BlueIshDan]

Also, I thought using ODBC would handle these indifferences?

 

[Rx_]

I think the answer is: it depends
The defination of True might actually be Is zero or is not zero or -1 depending on the DB. Then, it depends on the language. Since we use ODBC - VBA and TSQL.

The old vision of ODBC is not what it use to be. I blame the Nike' Generation of programmers. They follow the slogan of "just do it" instead of "think about it a little and have some kind of plan before you do it". But, it seems personal, corporate, and government budgets management use the Nike' philosophy - so why not ODBC?

The ODBC supplied by Oracle is much worse. If you link to a GIS system, the ODBC has some real challanges beyond data types such as duplicated non-primary keys.

Limiting ODBC to just Microsoft SQL Server Native Cleint 11.0 and MS Access ODBC - lets remember that the Cloud is the new "Gospel" and MS Access still has old dialogue boxes (e.g. Link Table Manager) from the 1990's. I remember when that dialogue looked Totally Awesom.

When using VBA for logic outside the Tables, it is up to the programmer to test values rather than trust ODBC.
For example: in my rule engine that checkes dozens of layers of business rules I migrated Integer and use -1 and 0 in the SQL Server tables. The company will talk about migrating SQL Server to Oracle then the other way around. An Integer is one thing that XML can understand if that is where things go.
Using the logic:
If MyRuleEngineIntegerVariable then Something
could become obsolete if the -1 isn't equal to True someday. So, the code
if MyRuleEngineIntegerVariable = -1 then Something
These Rules were prototyped in VBA. Once prototyped and tested, they were converted to TSQL. TSQL's source might be a Linked Oracle Server.
The TSQL function code is hundres of times faster than VBA and available centrally.

This is a horribly long answer for "it depends". It comes down to Business Scope, Lifecycle and maintence.
A Busines Rule Engine that supports Regulatory business decision, GIS Engineering desision, and Accounting Management needs to be centralized and adapt to changes across the enterprise.
An Access Report used to filter some criteria based on native True/False can go with the Bit and recover quickly should something change in the future.

For day-to-day T/F on Linked Tables, the Bit (with no Nulls) should work.
HOWEVER: I am interested in cases if this has been a problem!

This is my two Bits worth, another ancient artifact phrase.
It just shows how important a Data Type can be.
One plus One doesn't always equal Two for exceptionally large values of One.

 

[BlueIshDan]

Exactly the kind of long it depends answer I was looking for Thank you!

 

[RivenSkaye]

Using the logic:
If MyRuleEngineIntegerVariable then Something
could become obsolete if the -1 isn't equal to True someday. So, the code
if MyRuleEngineIntegerVariable = -1 then Something
These Rules were prototyped in VBA. Once prototyped and tested, they were converted to TSQL. TSQL's source might be a Linked Oracle Server.
The TSQL function code is hundres of times faster than VBA and available centrally.

I might be oversimplifying things here, but if your code depends on data from different sources with potentially different values for True and False wouldn't it be much more robust for your check to be:

If Not MyRuleEngineIntegerVariable = 0 Then ' Non-zero should be considered True for at least -1 and 1, with the former being a bitmask of all ones
    Something
Else ' Not True => False
    SomethingElse
EndIf

 

[Gasman]

9 year old thread?

 

[RivenSkaye]

The power of searching for an issue on the web and finding something actually relevant; the thread may be old but it's still mostly a solution for my problem. Save for the numeric check which is why I replied