Helpful Information
 
 
Category: Database Management
Access/VB SQL problem - bitwise operators?

Hi all,

I'm using an Access database with an external VB program, and one of the fields needs to store the type of user for each record. As I need to store 1 or more of 8 types, I set a bit per type - in VB just using a standard OR operator.

But in my SQL query to the database, this doesn't work (using AND) - the statement I'm currently using is:

SELECT * FROM users WHERE (UserType & 1)

where 1 is the bit value (eg. 1,2,4,8 and so on). This works fine in VB when I use it for other operations, I just can't select the right records - it returns True all the time.

Any ideas?!

Cheers :)

-rich

Well, I'm not completely familiar with how well Access does SQL, but I will note that what you are doing doesn't quite fit the bill for full database normalization. This is a perfect example of a programmer trying to use programming to solve a problem that has already been solved in realtional databases in another (better) way.

Rather than trying to embed multiple values in one field of the user's table, or trying to have a column for each setting, (which is an even bigger mistake), why not just use a simple external table, along with a simple validation table to hold these values?

1. I suppose your main table has a unique id column, right? So just create another table with 3 columns: a unique id (autonumber?), a foreign key (relationship) which relates to the unique id in your main table, and finally, the field which holds the user type:

ID | relatedID | usertype

Now, the beauty of this is that you can have multiple rows for each connected "main" record. Thus if a record on your main table has four usertype attribues, you create four rows in the "usertypes" table, one row for each type. Now, you can perform all kinds of nice aggregate queries on this table, to get better information about usertypes, quintities, statistics, etc...

2. The validation table is a small one-column lookup table, which has a row for each valid "usertype" value. This constrains possible entries in your "usertypes" table. This is actually a good thing, because now if you want to have another usertype value, you just add it to that small table, and your application now has 9 possible usertypes. If you want to change the definition of a usertype, you just change it in that small table.

It may seem like a bit more of a pain at first, but it will make your application much more flexible and scaleable in the end.

With all respect to all the database administrators out there, sometimes (for legacy reasons), creating a related table simply isn't an option. We programmers have to work with data structured the way it IS, not the way we'd like it to be.

Access' JET database does not support bitwise operations. You have to do it mathematically. So for AND, you'll need to use a modulus operation. To see if "UserType AND 1 = 1", instead check if "UserType mod 1 = 0", which will confirm that UserType is evenly divisible by 1.

E.g., if you want to check if the third bit of UserType is set, you would check "UserType mod 4 = 0", which will confirm that UserType is evenly divisible by 4.

If one of the columns of the table need to indicate user type,
why not store as a char(1) or smallInt in that column? Why do you have to resort to bit-fiddling in a relational database?

fv

With all respect to all the database administrators out there, sometimes (for legacy reasons), creating a related table simply isn't an option. We programmers have to work with data structured the way it IS, not the way we'd like it to be.

Access' JET database does not support bitwise operations. You have to do it mathematically. So for AND, you'll need to use a modulus operation. To see if "UserType AND 1 = 1", instead check if "UserType mod 1 = 0", which will confirm that UserType is evenly divisible by 1.

E.g., if you want to check if the third bit of UserType is set, you would check "UserType mod 4 = 0", which will confirm that UserType is evenly divisible by 4.
Grossly incorrect. ANY integer mod 1 will return 0. Modulus is more related to division (technically, subtraction). It repeatedly subtracts the right operand from the value of the left operand until that value is less than the right operand, then it returns that remainder. 10 mod 4 = 2 (10 - 4 = 6, 6 - 4 = 2).

This does not work at all toward checking bitwise values:
0001 mod 0001 = 0000
0010 mod 0001 = 0000
0100 mod 0001 = 0000
... and so on., because it just keeps subtracting 1 until the remainder is less than 1.

Likewise with the suggestion of mod 4 to check the 3rd bit:
0100 mod 0100 = 0000
1000 mod 0100 = 0000 (8 - 4 = 4, 4 - 4 = 0)

You'll never get the value you need for this information from a modulus operation. Since Access does not have bitwise operators, and if you have no other option than to use the bitfields for the usertype, then you'll have to check it in VB.

Just do a SELECT * FROM users, but loop through the results, checking each for the particular type(s) you're seeking:


Do While Not Records.EOF
If (Records!UserType And SeekType) = SeekType Then
'perform operations
End If
Loop
One day you will be very glad when you don't have to fiddle with Access anymore. And for the record, there's nothing wrong with using bitfields.

Edit:
As for the use of the UserType information, if the UserType is merely data, then the solution posed by rycamor is good. However, if these UserTypes are used internally by the program (eg, to limit what parts of the program the user has access to), then it doesn't do much good to be able to add that 9th type to the master types table. Also, to check the user's type for access permission to a part of the program, it's much easier to check a bit value like this:


If (currUser.UserType And AllowedType) = AllowedType Then
'go ahead with operations

Else
'deny access

End If
than to do it via the database:


Records.Open _
"SELECT * FROM usersTypes AS u" & _
" WHERE u.type IN (SELECT type FROM masterTypes)" & _
" AND u.userID = " & currUser.ID

If Records.RecordCount > 0 Then
'go ahead with operations

Else
'deny access

End If

Records.Close










privacy (GDPR)