Checkbox Failures: MS Access .adp Connected to SQL

[HumanJHawkins]

Hi all,

I have an Access Form in an .adp connected to MS SQL Server 2000. When
the field behind them is of the "bit" or "tinyint" datatypes, my
checkboxes will not uncheck and also exhibit other bad behavior such
as one checkbox affecting the data in another checkbox.

When I convert all of the datatypes to smallints, the problem goes
away and everything works perfectly.

In MS SQL, the "bit" and "tinyint" datatypes are always unsigned. This
issue appears to be related to Access attempting to store a -1 for
true instead of 1. However, Access (even using the exact same .adp
file) does not do this for other people in my office.

So, does anyone have any suggestions for what I should look at in
trying to find the cause of this issue? Is there some property of
checkboxes I should look at? Is it possible to get an MS Access DB
style checkbox (which uses -1 for true) onto a SQL connected form?

Thanks in advance for any help.

 

[Sylvain Lafontaine]

There have been many reports of problems with the bit & tiniyint datatypes;
especially but not limited to the 2000 version (but less problems with 2002
and much less with 2003; for 2007, I don't know). Some of these problems -
including the affecting of other data in the same record - seem to be
related to the use of the RecordsetClone function instead of the use of
Recordset.Clone.

Suggestions: 1) if you have either the 2000 or 2002 version, upgrade to the
2003 version (or 2007?). Make sure that the latest service packs have been
applied, both for Office and Windows.

2) if you use RecordsetClone in your code, consider replacing it with
Recordset.Clone.

 

[HumanJHawkins]

There have been many reports of problems with the bit & tiniyint datatypes;
especially but not limited to the 2000 version (but less problems with 2002
and much less with 2003; for 2007, I don't know). Some of these problems -
including the affecting of other data in the same record - seem to be
related to the use of the RecordsetClone function instead of the use of
Recordset.Clone.

<CUT>

Through further examination, we have determined that a bug or conflict
was introduced in service pack 3 for MS Access 2003. I am not sure
whether the bug is in SP3, or just that SP3 conflicts with something
else we (or possibly Dell) use.

SP3 works on many machines in our office, but after upgrading from SP2
to SP3, two laptops we have tried started experiencing this problem.

Use of a small int works around the issue. And in our case, the data
size will only increase by a few K. So this is probably the best
solution.

Thanks all.

 

[Sylvain Lafontaine]

If only a few machines have shown this problem, then you could try
decompiling the ADP file directly on these machines.