Cannot save a null to an optional integer field in MDB

[sanyock]

I connected OOBase to an MS Access MDB file created from ODBC manager. Everything seems working fine except optional fields. I was able to design a database structure. But when editing a table data it does not let me leave an optional long integer blank.

Would anyone please suggest how can this be fixed?

Note: optional text fields work as expected

[eremmel]

When I create a table in MS Access with Base and do not specify a default value I can insert empty integer values as well. I tested via ODBC and via native ms access interface both. You talked about the design of a database structure, is data including multiple tables and relations between the tables? Are you trying to enter empty data into a Foreign Key field?
You might post your (stripped) MS access database file and Base document and tell about the table/field you have trouble with.

[sanyock]

It actually seems to be another problem: OOBase does not save "optional" (nullable) attribute for a field.
I reopened the database in the designer and fields are "required" again though I marked them as "not required".
Trying to save them as "not required" does not help.
The files have been attached.

[eremmel]

You are right. You can not change the property from NOT NULL to NULL. You can have columns with NULL values when you add them to the table as new columns or create the table from scratch. You can not make two changes to a table at a time and you can not rename a column. I noticed in the past that it is dangerous to make changes to MS Access via the table/relationship/view management of Base (Accept for creating a table). So make backup copies of database and Base. I normally do those changes with SQL statements via Tools -> SQL ... . So when you want to change a this property of column (C1) you have to:

1. add a dummy column to the table
2. copy the data from C1 to dummy
3. drop the column C1
4. add the column C1 (with 'Entry required = No')
5. copy data from dummy to C1

When you have a look to the syntax of alter table alter column statement you can see that there is a NOT NULL clause but not a NULL clause. When you run such a alter table alter column statement to reset the NOT NULL clause by not specifying the NOT NULL phrase, then this does not work.

[sanyock]

You can have columns with NULL values when you add them to the table as new columns or create the table from scratch.

In my version of OOBase 3.1 NULLABLE attribute is not saved even if a new field is added. At the beginning it looks like a nullable but after restarting OOBase application and opening the ODB file with a link to the MDB file the field is not nullable anymore.

[eremmel]

The only thing left is that you try to add a column via alter table syntax (see link in my previous post)

Code: Select all

ALTER TABLE TestTable ADD COLUMN IntFieldwithNULL INTEGER;
ALTER TABLE TestTable ADD COLUMN IntFieldnoNULL INTEGER NOT NULL;

Note: I noticed that I can not empty a integer field on a nullable column when I open a table to edit it or via a form. But I can do it via SQL

Code: Select all

update Table1 set i1=null where ID=3

You might consider to join the group of SQL programmers that hate the NULL and will never use it in their design...

[sanyock]

You might consider to join the group of SQL programmers that hate the NULL and will never use it in their design...

Null is convenient to have a "no reference" foreign key value.

Btw, OOBase+PostgreSQL work fine with nulls, but I could not find an "under $50" design tool for database diagraming.

Does anybody aware of such a tool like http://www.microolap.com/products/datab ... -designer/
but priced under $50?

[eremmel]

But NULL is as convenient as 0 or -1 or ... as no FK relation.

[sanyock]

But NULL is as convenient as 0 or -1 or ... as no FK relation.

Sometimes it is convenient to press del key to clear a reference in a record instead of typing in a predefined value like 0. Also corresponding 0 record should be explicitelly added to a table with a referenced primary key which is an additional work during prototyping a db design.