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.
我们使用MS Access作为Oracle表的前端,通过ODBC,它一直运行良好。但是我们试图在Oracle表中使用DEFAULT约束。当我们在Access中打开链接表时,我们看到现有数据就好了,但是当我们尝试添加一行时,没有将任何值键入具有Oracle DEFAULT的列(期望使用默认值),我们看到#在每列中删除,该行不会添加到表中。有任何想法吗?如果有帮助我可以提供更多细节,请告诉我。
2 个解决方案
#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.
如果您使用网格视图作为数据条目执行此操作,我认为Access可能明确尝试插入空字符串作为该值。尝试为插入编写纯SQL语句,看看会发生什么。
I'd expect underlying SQL like this (assuming default values set for name="John", balance="0.0")...
我希望这样的底层SQL(假设为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:
但是,通过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.
我假设SQL示例使用未设置列的默认值,但网格视图从UI提供空白值,这将阻止使用默认值。
#2
Dunno about Oracle, but to make this work with SQL Server you need a timestamp field in your table.
关于Oracle的Dunno,但要使用SQL Server,您需要在表中使用时间戳字段。
#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.
如果您使用网格视图作为数据条目执行此操作,我认为Access可能明确尝试插入空字符串作为该值。尝试为插入编写纯SQL语句,看看会发生什么。
I'd expect underlying SQL like this (assuming default values set for name="John", balance="0.0")...
我希望这样的底层SQL(假设为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:
但是,通过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.
我假设SQL示例使用未设置列的默认值,但网格视图从UI提供空白值,这将阻止使用默认值。
#2
Dunno about Oracle, but to make this work with SQL Server you need a timestamp field in your table.
关于Oracle的Dunno,但要使用SQL Server,您需要在表中使用时间戳字段。