Access via ODBC - Oracle DEFAULT not working

Question

We use MS Access as a front-end to Oracle tables, via ODBC, and it has been working well. But we're trying to use the DEFAULT constraint in an Oracle table. When we open the linked table in Access, we see existing data just fine, but when we try to add a row, without keying any value into column(s) that have an Oracle DEFAULT (expecting the default to be used) we see #Deleted in each column and the row does not get added to the table. Any ideas? I can supply more details if it would help, just let me know.

Answer 1

If you're doing this with the grid view as data entry, I'd think that Access may be explicitly trying to insert an empty string as that value. Try writing the plain SQL statement for the insert and see what happens.

I'd expect underlying SQL like this (assuming default values set for name="John", balance="0.0")...

Via grid view:

insert into customers (cust_id, name, balance) values (1, "Bob", 50.25);

and if one were 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'd assume the SQL example to use the default values for the unset columns, but the grid view to supply blank values from the UI which would prevent the defaults from being used.

Answer 2

Dunno about Oracle, but to make this work with SQL Server you need a timestamp field in your table.