Compact function creates Nulls

S

Stephen Haley

I am not sure which group this belongs in so have posted here.
I have a 2002/03 DB with a table with several boolean fields. When I compact
it in access 2003 using tools-Database utilities->compact & repair it sets
any boolean field that is not TRUE to NULL wich I have now had to set my
code to handle.

Why is this happening - I am sure it has never happened before in 2002 (Just
moved to 2003). I have a fully updated system running all SPs & patches.
Surely this cannot be right.

rgds
Stephen
 
A

Allen Browne

If your tables are Access tables (not attached tables from some other
database), then the yes/no fields cannot be Null.

They might appear as Null when you are at a new record of you have not
defined a Default Value for the field.
 
D

david epsom dot com dot au

I think that the file is damaged. Can you import
everything into a new file? Does that help?

(david)
 
S

Stephen Haley

The fields in question are definately null when the record is selected but
the fields had been created/added after the record in ? was created. Like
you I was under the impression that a yes/no field could never be null.
rgds
Stephen
 
A

Allen Browne

Stephen, even if you create a yes/no field in an existing Access table,
after other records have been created, they cannot be null.

What version of Access are these tables?
Are they attached tables?
If so, are the attached Access tables?
It is the version of the back end I need to know.

If you have found a way around that limitation I am interested in
investigating it.
 
S

Stephen Haley

I dont think of it as a limitation mostly a pain as I now have to NZ every
boolean on the just in case principal. If I want a 3 state boolean (Y/N/DK)
I use an integer.
What version of Access are these tables? 2002/03
Are they attached tables? yes
If so, are they attached Access tables? yes
It is the version of the back end I need to know.
Click to expand...
Both back & front are mdb 2002/03 format

Table has one record (its my static setup data store) and form is connected
using "SELECT tblStaticSetup.* FROM tblStaticSetup;" in the form
recordsource
OnLoad event code in form is now definately seeing these fields as null
after the compact and false before.

Will see if the same thing happens if I use ado to interogate from the FE
and if so then if the same thing happens from the BE.

rgds
stephen
 
B

Brendan Reynolds

Could you post the code?

--
Brendan Reynolds


Stephen Haley said:
I dont think of it as a limitation mostly a pain as I now have to NZ every
boolean on the just in case principal. If I want a 3 state boolean (Y/N/DK)
I use an integer.

Both back & front are mdb 2002/03 format

Table has one record (its my static setup data store) and form is
connected using "SELECT tblStaticSetup.* FROM tblStaticSetup;" in the form
recordsource
OnLoad event code in form is now definately seeing these fields as null
after the compact and false before.

Will see if the same thing happens if I use ado to interogate from the FE
and if so then if the same thing happens from the BE.

rgds
stephen
Click to expand...
 
A

Allen Browne

Ah, this is an Number type field (size Integer), used so that you can store
a triple-state. Thanks Stephen, now I understand at least how the issue
could arise.

If you have a Default Value of 0 on this field in the table, the field will
default to False when you enter a new record. If you add a new record via an
Append Query without explicitly assigning a value to this field, Access
should insert the default value. I actually don't trust it to do that, so I
always specify the value in the append query statement anyway, but I can't
be sure if that mistrust was based on an issue that was fixed in a service
patch or just on paranoia. :)

Presumably you are interfacing this control with a check box that has its
TripleState property set to Yes. If you are using Access 2003 on Windows XP,
you will see no visible difference between False and Null. Since you cannot
distinguish them, it is possible to be tricked into believing that the field
contains a False, when it fact it contains a Null. You can avoid this and
see the third state if you uncheck the box under:
Tools | Options | General | Use Windows Themed Controls

I doubt that the Compact is actually changing existing values from Null to
False, unless the mdb is corrupt.

Hope that helps.
 
S

Stephen Haley

It is an Int field (thought it was a y/n originaly) but it is via code that
the diff is occuring.
before compacting the code is showing false for that field
after compacting the code is showing null
Cannot see any evidence of corruption
rgds
stephen
 
Top