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