January 17, 2006 at 2:46 pm
In Access you got boolean fields. What are the correspondent in SQL Server? The import wizard converts them in "bit" type, but then a sentence like this (visual basic):
" where PreferredCustomer=True"
has to be turned
" where PreferredCustomer=1"
Am I right? Shall I change all the pieces of code where I use boolean fields and substitute the True or False values by 1 or 0????? It makes VB not very portable accross those two platforms (and they are cousins one of each other)!!!
thanks
January 18, 2006 at 2:49 am
The similar data type in SQL is the bit datatype. It can hold the values 0, 1 or NULL (if NULLS allowed)
You can query it with
where MyBit = '1'
or
where Mybit = 1
January 18, 2006 at 4:49 am
=0 is false
<>0 is true
January 18, 2006 at 6:41 am
Where MyBit = 1, Where MyBit = 0 are good for testing bits. Using MyBit <> 0 instead of MyBit = 1 can change how the optimzer estimates the rowcount, which might be a good thing or a bad thing.
It is even more correct to use Where MyBit = convert(bit,0) or MyBit = convert(bit,1). The optimizer will assume that the literal 0 or 1 is an integer, and will convert each bit that it has to test to integer before perfoming the comparison. Converting the literal to a bit will result in a slightly faster execution plan, because the comparison will happen without the convert operation.
I think this is a bug, but I'm sure that it is classified as a feature.
Also, if you have an index on the bit column, the optimizer can use that index for a seek operation if the literal is converted to a bit. There are rare situations in which a bit should be indexed, but when such a situation arises, this is a handy thing to know.
hth
jg
January 18, 2006 at 7:18 am
It depends on how you're handling the SQL tables. If they are linked via ODBC, then your Access SQL statements will still work as "MyBit = True" or "MyBit = False." Open an ODBC linked table in Access and the bit column presents as 0 or -1 just like an Access Boolean. Even though the underlying database is SQL Server, you're accessing it through JET, so nothing really changes in VBA/Access SQL.
If you use ADO in VBA to submit SQL to SQL Server, then the SQL has to be standard SQL rather than Access SQL and the parameters will have to be 0 and 1.
Access Projects (ADP files) are generally ADO. I've not messed with these much, but I think you'll have to use standard SQL Server SQL with bits = 0 or 1 in an Access ADP.
My usual approach is a mix of linking tables via ODBC in an MDB for bound forms and using ADO in VBA to make stored procedure calls and for doing more complex data processing than simple data presentation via bound forms.
January 18, 2006 at 7:51 am
If you're coding in Access, I'd say stick with tests against 0. They work regardless of how True is represented. Test for "=0" to find False; test for "<>0" to find True.
If you use the SQL shown below, you can examine all of your Access queries to see if you've used -1 or True:
SELECT o.Name, q.Expression, q.Name1, q.Name2
FROM msysobjects AS o INNER JOIN msysqueries AS q ON o.Id = q.ObjectId
WHERE (((o.Name) Not Like "~*") AND ((q.Expression) Like "*-1*" Or (q.Expression) Like "*true*"));
You can narrow the return by using more restrictive tests, but start by casting a wide net, then narrow down. This helps you find where you need to change your tests, so that you can make your saved queries consistent with the SQL you'll be writing in VB.
Working in VB to write SQL you won't have occasion to use convert(bit,0) unless you're writing SQL to use in a pass-through query. Queries on the Access side of things don't know what "convert(bit,0)" means. You'll get an error.
One tip: This is the sort of issue that can generate a lot of discussion that escalates way past the practical significance or the difficulty of dealing with it. Pick a method, any method, and be consistent. As with so many things, it doesn't matter so much WHAT you do, as long as you KNOW what you are doing.
January 18, 2006 at 5:57 pm
I use an integer field in SQL Server rather than the bit field. This allows Access to continue storing the 0 and -1. If you like, add check constraints or create a user defined datatype.
January 18, 2006 at 8:06 pm
Not sure if this "bug" in Access has been fixed or not because I still work around it - always!
It used to be that if you had a Bit column in a SQL Server table and linked to that table from an Access front-end, you get a strange error message when trying to update a row that says another user is editing the same record. You then get the choice of discarding the data or saving it locally. There was no other user editing the data. What was happening is that a Null value was stored in the bit field and for some reason Access couldn't deal with it.
The workaround is quite simple. Set the bit field default value to (0) and don't allow Nulls. If converting a table from Access to SQL, make certain that the existing data contains either a 0 or 1 (-1 if checking the values before conversion). That eliminates the little agrevation that can eat a day when looking for that offending user.
Bill
Chattanooga, TN
January 25, 2006 at 9:00 pm
Thanks for that reminder, Bill Holt. I've seen that before but had completely forgotten about it and would have fallen for it again. I'm adding that to my "Access folder".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply