SQL Server Bit Fields & Bound Form Fields Incompatibility
Micah Miller
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 news: 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 fill the blanks, no spam please
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.
"Micah Miller" <micah....@privacy.me> wrote in message
news:euMhJzFF...@TK2MSFTNGP09.phx.gbl...
Bill Patten
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
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:O0QtHKGF...@TK2MSFTNGP15.phx.gbl...
J. Clay
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" <micah....@privacy.me> wrote in message
news:euMhJzFF...@TK2MSFTNGP09.phx.gbl...
Micah Miller
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
"J. Clay" <jim@N0_LCPmugs_Spam.com> wrote in message
news:eSNMQ2GF...@TK2MSFTNGP09.phx.gbl...
Malcolm Cook
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
"Micah Miller" <micah....@privacy.me> wrote in message
news:%23qj$VTHFF...@TK2MSFTNGP12.phx.gbl...
Sylvain Lafontaine fill the blanks, no spam please
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.
"Malcolm Cook" <m...@stowers-institute.org> wrote in message
news:eC2ntWUF...@TK2MSFTNGP12.phx.gbl...
Micah Miller
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" <micah....@privacy.me> wrote in message
news:euMhJzFF...@TK2MSFTNGP09.phx.gbl...
Micah Miller
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
"Malcolm Cook" <m...@stowers-institute.org> wrote in message
news:eC2ntWUF...@TK2MSFTNGP12.phx.gbl...
J. Clay
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
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23TRnmcU...@tk2msftngp13.phx.gbl...
Malcolm Cook
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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23TRnmcU...@tk2msftngp13.phx.gbl...
Sylvain Lafontaine fill the blanks, no spam please
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.
"Malcolm Cook" <m...@stowers-institute.org> wrote in message
news:OINBEncF...@TK2MSFTNGP12.phx.gbl...
Roland Alden
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 fill the blanks, no spam please
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" <ral...@ralden.com> wrote in message
news:eEOcERFI...@TK2MSFTNGP10.phx.gbl...
Roland Alden
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. A
pox on them. Can't they get a simple thing right like a checkbox bound
to a bit column?
What else am I supposed to do when I want to store a yes/no value and
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 them
SourceSafe and the fact that it would take costly development time. But
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 Microsoft
for their ineptitude and stupidity.
--
ESquaredPosted from - http://www.officehelp.in
Sylvain Lafontaine fill the blanks, no spam please
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"ESquared" <ESquare...@NoSpamPleaze.com> wrote in message
news:ESquare...@NoSpamPleaze.com...
Robert Morley
bound bit fields, though that certainly doesn't mean they don't exist. :)
Rob
"ESquared" <ESquare...@NoSpamPleaze.com> wrote in message
news:ESquare...@NoSpamPleaze.com...
>
ESquared
Did you say "continuous form?" I thought you did.
Thanks for the attempt to help, anyway. :)
Sylvain Lafontaine fill the blanks, no spam please
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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"ESquared" <ESquare...@NoSpamPleaze.com> wrote in message
news:ESquare...@NoSpamPleaze.com...
>
ESquared
I did finally find a workaround, although I don't know how globally this
can be applied, as I've only used it on one form so far. I sure hope it
helps someone avoid some frustration.
Access (sometimes?) runs certain events in this order when you click on
the checkbox:
Control_GotFocus
Form_BeforeUpdate
Form_BeforeUpdate (yes, twice, and in certain instances all the primary
key autonumber columns have sequential values starting at 1 the first
time, then have their proper server-side values the second time, but
that's not related to this issue.)
Control_BeforeUpdate
Chb_AfterUpdate
Control_Click
The crash always happens immediately after one of the form_beforeupdate
events (and before the control's beforeupdate event). If both of those
beforeupdates cancelled then the crash does not occur. So my workaround
was surprisingly simple:
Declare a global boolean, something like gfPreventUpdate.
* In the control's GotFocus event:
gfPreventUpdate = True
* In the form's BeforeUpdate event:
Cancel = gfPreventUpdate
* In the control's BeforeUpdate, AfterUpdate, or Click event (whichever
is convenient for you):
gfPreventUpdate = False
Erik
aaron...@gmail.com
Microsoft is a bunch of assholes that WONT FIX BUGS IN THEIR SOFTWARE
SCREW YOU MICROSOFT
I think that we should contact Ralph Nader and ask for a Class Action
Lawsuit.
i mean-- those assholes; I've been bithcing about this stuff for years
the only problem that Microsoft has is that they're not FORTHCOMING and
HONEST about bugs.
We need an independent company to keep track of all of these Access
bugs and Force microsoft to fix them.
Oops, I'm sorry did i make some FAT LAZY PROJECT MANAGER AT MICROSOFT
SAD TO READ THIS??!!??
GET OFF YOUR ASS AND START FIXING BUGS MICROSOFT
IN ___THIS____ VERSION OF OFFICE YOU ASSHOLES
ESquared
There also needs to be a gfPreventUpdate = False in the
Control_LostFocus event because someone could tab to the control and
away from it without using it. Also, if any weird update-preventing
situation arises, consider whether some other event also needs to have
this flag set to false to provide normal function.
aaron...@gmail.com
FIX YOUR BUGS MICROSOFT
I've been screaming bloody fucking murder about this for years now