ODBC access - Oracle DEFAULT not working
We are using MS Access as our Oracle front-end table via ODBC and it works well. But we are trying to use DEFAULT constraint on Oracle table. When we open the linked table in Access, we see that the existing data is fine, but when we try to add a row, without entering any value into the columns (s) that have an Oracle DEFAULT (waiting to be used by default), we see #Delete in each column and no row is added to the table. Any ideas? I can provide more details, if it helps, just let me know.
If you are doing this with a grid view as your input, I think Access might explicitly try to insert a blank row as that value. Try writing a simple SQL statement to insert and see what happens.
I expect the underlying SQL to be similar (assuming default values are given for name = "John", balance = "0.0") ...
Through the grid view:
insert into customers (cust_id, name, balance) values (1, "Bob", 50.25);
and if one of them is empty:
insert into customers (cust_id, name, balance) values (2, "", 0);
But via SQL:
insert into customers (cust_id, name) values (3, "Pete");
insert into customers (cust_id) values (4);
I would suggest that the SQL example uses defaults for unset columns, but a grid view to represent empty values from the UI, which would prevent the default from being used.
source to share