Boolean - Access Linked Tables to SQL Server - Data Types (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 13:41
Joined
Oct 22, 2009
Messages
2,803
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));
 

Attachments

  • True-False-Data-Type.jpg
    True-False-Data-Type.jpg
    23.2 KB · Views: 225

Ranman256

Well-known member
Local time
Today, 15:41
Joined
Apr 9, 2015
Messages
4,339
We tried this, but sometimes, SQL svr would error out on BIT fields.
It really hated BIT fields.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:41
Joined
Aug 30, 2003
Messages
36,118
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_

Nothing In Moderation
Local time
Today, 13:41
Joined
Oct 22, 2009
Messages
2,803
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

Well-known member
Local time
Today, 15:41
Joined
Apr 9, 2015
Messages
4,339
I think it errored ONLY IF, a field was changed to BIT that wasnt originally. (formly,integer)
But we lived fine with integer.
 

BlueIshDan

☠
Local time
Today, 16:41
Joined
May 15, 2014
Messages
1,122
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 :p
 

BlueIshDan

☠
Local time
Today, 16:41
Joined
May 15, 2014
Messages
1,122
Also, I thought using ODBC would handle these indifferences?
 

Rx_

Nothing In Moderation
Local time
Today, 13:41
Joined
Oct 22, 2009
Messages
2,803
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

☠
Local time
Today, 16:41
Joined
May 15, 2014
Messages
1,122
Exactly the kind of long it depends answer I was looking for :) Thank you!
 

Users who are viewing this thread

Top Bottom