[AccessD] db execute on linked vs local table
Paul.W at industrialcontrol.co.nz
Mon Jan 31 20:28:38 CST 2022
There is general agreement that FALSE = 0.
In the absence of any generally accepted definition of TRUE (other than "<>
FALSE" or "NOT FALSE") I refuse to write code that contains "TRUE". Maybe
it keeps me out of unnecessary trouble.
Sometimes TRUE gets turned into 1 and sometimes it gets turned into one of
the versions of -1 (with any number of bits). Sometimes what you get isn't
what you need.
You haven't stated whether the back end table is Access or something else.
It can make a difference.
Some also believe in avoiding the yes/no field type in Access tables (
http://allenbrowne.com/NoYesNo.html) so perhaps you are using a numeric
On Tue, 1 Feb 2022 at 10:14, Ryan W <wrwehler at gmail.com> wrote:
> More info:
> If I set the column to not allow nulls, db.execute tries to set it as NULL
> but then a prepared statement comes in after the fact and does indeed set
> it to true.
> On Mon, Jan 31, 2022 at 2:40 PM Ryan W <wrwehler at gmail.com> wrote:
> > Upon more testing it seems like FALSE (without quotes) works as well. It
> > just seems to be TRUE (no quotes) sets my remote bit column to NULL.
> > Here's a SQL Profiler:
> > https://i.imgur.com/rVx3vmX.png
> > On Mon, Jan 31, 2022 at 1:48 PM Ryan W <wrwehler at gmail.com> wrote:
> >> Is there a reason db.execute syntax is a bit different on a linked
> >> vs local (DAO?)
> >> db.execute "UPDATE tbl SET col_A = TRUE" works as expected locally.
> >> where run on a linked table, it NULLs out the bit field.
> >> but db.execute "UPDATE tbl set col_A = 'TRUE'" or db.execute "UPDATE
> >> set col_A = 1" works remotely and the numerical representation of
> >> true/false works both remote AND locally.
> >> However 'TRUE' or 'FALSE' with the single quotes on a local table gives
> >> data type mismatch (which makes sense, because it's treating it as a
> >> literal and not converting it to 1/0.
> >> I guess my question is, is it the difference in the DAO engine vs the
> >> ODBC layer that is turning my BIT field NULL (linked) versus local?
> AccessD mailing list
> AccessD at databaseadvisors.com
> Website: http://www.databaseadvisors.com
More information about the AccessD