SQL Server Bit Fields & Bound Form Fields Incompatibility

[Micah Miller]

At the firm I am currently employed we have been developing an Access 2002
ADP with SQL Server 2000 database. We ran into some issues with binding
check/radio buttons to the bit fields. Upon changing the value the
application would crash. Initially, I believed a few things to be causes,
either too many bound bit fields, which was not the case scenario after some
research, and the SQL Server bit field is like the rest of they binary world
reading 1 as true and 0 as false, VB reading -1 as true and 0 as false.
After researching on the web I didn't find much of anything in regard to
bit-to-boolean field binding causing an application crash. Finally, my
supervisor call MS tech support and eventually got to some guy (he could
barely understand) telling him that this is unsupported. We then asked for
documentation regarding this to see it in writing, we then were told that
this information is not public and that it won't be made public until at
least 4 others have experienced this problem.

The good we didn't get charged for that.

Ok... UNSUPPORTED! What is that!? How many copies of OfficeXP Developer
were sold?! Of those sold you can't be telling me that we are the only ones
that ran into this issue! Unless they feel they're going to be charged an
"arm and a leg" to call support. Besides the fact that this helpful piece
information would have been nice if it were made public so we could have
known this BEFORE investing time in developing. What is this?! This is not
the only time I ran into the its not "public" issue. So instead of fixing
the problem with the software incompatibility (it's suggested we use SQL
Server 2000 with ADP's both Microsoft products), I basically would have to
write my own class to act as an Interface between the database and that
form, or better yet change all of my bit fields to int or tinyint (then I
would probably have to type then as Booleans anyway)?! So, what I
understand is that I have to spend my company's time & money to develop
something like this when a fix could be developed in as little as a few
hours to days? I take it that the phrase "That's not supported" are the 3
keywords to get out of fixing broken software?! Great job on the customer
service you guys at Microsoft! Great Job! If Microsoft were some small
business this would be completely absurd. On the other hand, I understand
that management and sales may push for products to be released and
developers don't get the chance to put everything into it by release date,
but come on! I'm not quite buying the slogan "Your potential. Our passion."
anymore here. If that were the case the software would get fixed!

I'm sorry I don't mean to flame here guys, but it's always the little things
like this that can thwart the whole development path. It's this kind of
experience with a company that drives its customers away. I doubt anything
will even be done regarding this somehow unknown issue unless there's a
profit margin or threat to future sales of the product involved. No wonder
people like the Open Source paradigm on application development - If it's
broke, you can fix it. Honestly, I'm starting to rethink future projects
now and the aroma of Java is smells so good.

Regards,

Micah

 

[Sylvain Lafontaine]

I had exactly the same problem as you with Access ADP 2002 after the
installation of SP2 for WinXP. I traced it back to the use of
RecordsetClone mainly but I had to suffer data corruption as well (I don't
remember exactly since it has been 6 months).

My only recommendation is either to upgrade to Access 2003 - no more problem
with it - or to remove SP2.

S. L.

 

[Bill Patten]

I had the same problem, if I used the navigation button I did not get the
error. If I used the wizard generated find record with a combo box, I had
the problem. My solution was to change the forms record source to select *
from xyz where id = " & combo instead of me.recordset.clone.

I didn't associate it with clone, so it's nice to know. (I didn't have the
problem in forms where I set the records source so just switched it over.

Bill

 

[J. Clay]

I have posted this problem in the past as well with little to no response.
I have noticed recently that this has not been happening. Not sure why.
The one thing I do make sure is that all Bit fields have a default value
(either 1 or 0) and that they are Not allowed to be Null. Also, anytime I
update an unbound check box to the database, I have a function Bln2Integer
that I run converting the Check box boolean values of True/False to either 1
or 0.

The other issue that I have seen in the past is if you directly affect the
form's underlying recordset directly (i.e. me.recordset.field = ???), rather
than using the form controls or SQL statements to change values. I don't do
this, but in my research I found several references to this being an issue.
Especially if you try and manipulate the underlying recordset during the
OnCurrent event.

I had found some references seaching Google Groups, but very limited.

HTH,
Jim

 

[Micah Miller]

We have set all the bit fields to have defaults of 0. So, that isn't the
issue there. As far as manipulating the underlying recordset, not in this
case. The recordset automatically saves the current record once the form is
closed or you move to another record. There is nothing manipulating the
underlying recordset in an OnCurrent event. Click one check box, go to
another record change that check box... Bam! Non descriptive error
message - even the tech support guy thought it should read more than "There
has been a problem the application will now close... Sorry for the
inconvenience." And send to Microsoft. I lost count of how many of these
things I have sent them. But, seriously it would take a miracle for anyone
at Microsoft to look at this known issue. If I'm right I believe I have
found four other people with the same problem... Isn't that sad, it's like
I have to prove my case to Microsoft before the even initiate a notion to
think about fixing it....

For the Boolean-to-Bit conversion you can basically just do an Abs() on the
boolean True/False value to hand it to SQL. I have also created a
FormatSQLValue() function to output values for use in a query string.

I have just had it up and over my head with all the run arounds/work arounds
I'm being given instead of having the source of the problem fixed. I didn't
write a line of code for the Access application, but I'm the one whose
supposed to be flexable and work with it because I paid for it?! Hello?! If
I treated some of my customers with their websites with kind of thing, I
would be out of business. Why does everyone sit back and take this when it's
completely unpractical?! They may have developed it but I have to work with
it every day with and deal with all the headeaches which I would have to
prove to Microsoft that their software is the cause of my suffering. I
can't wait until I'm done with the Access Runtime platform!

--Micah

 

[Malcolm Cook]

For some reason I had not been bitten by this bug until I read this message!
I mean, I have a deployed Access 2K ADP against SQL Server 2000 with
checkboxes bound to bit columns and no crashing. But, today, just to see if
I could see why I was NOT getting this and you and others were, I tried it
in my dev copy, and, KABOOM.

So, after trying to figure out what changed, trying to change from bit to
tinyint or smallint in the back end, ensuring I had defaults in the backend
(and the access checkbox for good measure), adding and deleting timestamps
from the backend table, trying views, stored procs, table valued functions,
and direct table access as the form's record source, I COULD NOT MAKE IT GO
AWAY AGAIN.

However, upgrading to 2003 did make it go away. I observed that the
adodb.field corresponding to the bit column is now of type adSmallInt, where
it used to be (AC2002) of type adBoolean. And the value of the checkbox,
the adodb.field, and the access.field all toggle in step between -1 and 0.
No booleans, trues, or falses in sight.

OK. So, I'm here now. Now to wonder about packaging the application for
distribution using developer extensions 2003.

Or, any one there want to advise me to avoid going this way...

Cheers,

Malcolm

 

[Sylvain Lafontaine]

Pretty good virus, huh?


Fun apart, have you installed the SP2 for WinXP recently?

For the bit column, there is not really such thing as a boolean type in
SQL-Server. The bit column can have the value 0 or 1 but these values
cannot be directed used as False and True.

The -1 and 0 are the logical values for True and False under VBA.

S. L.

 

[Micah Miller]

I see that Malcolm has come to the same conclusion that we have. I suppose
I was right that the problem wouldn't get fixed unless there is a profit
margin involved! We had purchased a copy of ACC2003 and guess what....
That's right ladies & gentleman, it worked!

Of course Microsoft would never outright admit this. Is this Deja Vu? I
think I've seen this pattern before...

So it's official Access 2003 IS the only FIX to the boolean-to-bit problem
in Access.

I take it this is one of those great Microsoft features that will remain
private FOREVER, but only to be discovered by waried developers.

--Micah

 

[Micah Miller]

Malcolm,

Not sure what to tell you at this point. In having no other choice but to
BUY new software in order to FIX BROKEN software, I want to get off the
Access front-end platform period. I'm sure as time goes on we will find
some more of those undocumented features of Access 2003 that may stand in
the way and would have to PURCHASE Access2006 to FIX those problems too. If
this is ok for you, go for it! As for me, the sooner were off this
platform, the better. Besides, Microsoft has been professing to KILL DAO
for years. With that being the likely to happen sometime in the near
UNKNOWN future, do you really think Microsoft will give any kind of warning
DAO/Access dead other than about a month before it's not "supported"? Just
think how angry the customers would be who just bought it a week before that
announcement...

I don't mean to be on a smear campaign, but this was the straw for me... I'm
done.

Regards,

Micah

 

[J. Clay]

Sylvian

As an FYI, we cannot run SP2 in our environment. I am highly integrated
with Goldmine and SP2 causes major issues between ADO and the BDE that
Goldmine uses for data access.

Now that I think about it some more, about the time that we put new
computers on everyones desk here w/Win XP this quit happening as
often...Hmmmm...

Not sure if there is any significance there, but something to think
about....

Jim

 

[Malcolm Cook]

Sylvain,

I'm running XP Professional Version 2002 SP1 with Access 2002 SP3.

Does that explain anything to you?

Cheers,

--Malcolm

From what I've read

 

[Sylvain Lafontaine]

No.

From my side, I have associated this bug with the installation of SP2 for
WinXP; however the installation of SP3 for Access 2002 could also have
installed the same bad version of library. It is also possible that this
bug was present since the beginning of A2002 but I don't remember having hit
it before or having read any message about it before SP2 (or SP3 in your
case). Don't forget that following bugs is not an easy task.

As this bug seems to be corrected in A2003; I won't take the time to test
this hypothesis any further.

S. L.

 

[Roland Alden]

Not to rain on this old parade but I'm using 2003 and ran into a very
similar problem; do not for a minute think upgrading alone will solve this.
My table had bit fields and simply creating a control on a form and saving
the form will cause Access to crash. MSFT took my table schema and
reproduced the problem and also advised me there were known issues with
bitfields in Access. Maybe I'll be the fourth guy

I have developed under Access circa 2000 and this is my first project in
2003. There is no doubt in my mind that 2003 represents a step backwards in
at least a few areas.

I too am looking around for a better front end development solution.

 

[Sylvain Lafontaine]

First, there are many bugs with ADP2003 as well as with any other previous
versions of ADP. When are dealing with ADP, you must learn your way to
navigate through these.

However, with the latest service packs for Windows (SP2) or for Office XP,
it looks like that ADP2002 seems to have reached a new low level in term of
reliability. I never say that upgrading to A2003 solved all problems of ADP
but simply that it didn't have many of these *new* bugs of A2002 (bugs that
weren't part of it two years ago but have popped out from nowhere in the
last year).

Just out of curiosity, are you on WinXP or on Win2003 ?

S. L.

 

[Roland Alden]

2003. I too have observed the giant step backwards in Access.

I guess I don't fully understand what ADP is all about. It seems to me that
it is based on a direct view onto the server with no intermediate layers of
database engine built into Access. You don't have any kind of two step
process of establishing linkages to tables, etc. Unlike other flavors of
Access' access to an SQL Server (I forget the exact terminology) views and
tables are not homogenized into the same thing in Access; you see what the
server is doing. A view is a view and a table is a table.

Some of the bugs I've noticed have to do with underlying semantic
differences between my ADP project and SQL itself. For example, if you use a
uniqueidentifier field in SQL and allow it to be null and you insert a new
record with default values for that field, in SQL server the new fields will
be null. This makes sense. Access on the other hand will generate its own
uniqueidentifier and stick it in there for you. This is wrong and true even
when there is a FK constraint on the underlying field (which Access can
see). The insert fails and you have to hand-code around this in Access. I've
discussed this with Microsoft and they acknowledge it as a bug which can't
be fixed because doing so would break a lot of other stuff having to do with
replication.

I can see how that might happen but it's very unfortunate that what should
have been a thin veneer of forms on top of SQL is just a bug infested swamp.
Right now I'm looking at Windows Forms in C# and it is about 1000 times
harder to do the simple things that Access does almost automatically.

 

[ESquared]

I call down a curse upon Microsoft and their nasty product, Access.
pox on them. Can't they get a simple thing right like a checkbox boun
to a bit column?

What else am I supposed to do when I want to store a yes/no value an
let the user manipulate it? Use a combo box - 0;No;1;Yes?

We cannot go from Access 2002 to 2003 for various reasons, among the
SourceSafe and the fact that it would take costly development time. Bu
how much development time have we lost in chasing this stupid bug
trying workarounds, working our brains out when it should just WORK.

I have important projects I'm trying to get done and I curse Microsof
for their ineptitude and stupidity

 

[Sylvain Lafontaine]

Well, you can use an unbound checkbox and set its value when entering the
current record and while leaving it. Not a perfect solution and won't work
easily with continuous form but maybe this suggestion can help you.

 

[Robert Morley]

What, exactly, is the problem? I don't REMEMBER having any problems with
bound bit fields, though that certainly doesn't mean they don't exist.



Rob

 

[ESquared]

Did you say "continuous form?" I thought you did.

Thanks for the attempt to help, anyway. :

 

[Sylvain Lafontaine]

Honestly, the only solution is probably to go with A2003.

I don't remember all the details of this problem on A2000 and A2002 but I
have enough problems with A2003 so that I don't have the time and energy to
think about the problems of older versions.