Writing into external table not possible

[M. Schroeder]

I use an Access-form to write into an external Oracle table via Oracle-ODBC
driver.
It is not a read-only connection, and batch autocommit mode set to "all
successful statements".

I can read all records from table, but update, insert, and delete is allowed
on form level, but I cannot change records and I cannot insert. Whats wrong?

Thanks,
Schroeder

[Rick Brandt]

M. Schroeder wrote:

I use an Access-form to write into an external Oracle table via
Oracle-ODBC driver.
It is not a read-only connection, and batch autocommit mode set to
"all successful statements".

I can read all records from table, but update, insert, and delete is
allowed on form level, but I cannot change records and I cannot
insert. Whats wrong?

Thanks,
Schroeder

Does the Oracle table definition include a Primary Key or a Unique Index? If
not then when creating the link you should be getting prompted for the fields
that can be used to build a local unique index. Without one of these three
things an ODBC link will not be editable.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[M. Schroeder]

> Does the Oracle table definition include a Primary Key or a Unique Index?
If

not then when creating the link you should be getting prompted for the fields that can be used to build a local unique index. Without one of these three things an ODBC link will not be editable.

(There was index and PK created, but I got nor prompt on creating the link)
But I took another table, with no primary key and no index.
Now I can insert records, but after closing the form they are not
persistent.
On re-opening the form the records are gone.

Looks to me as if the commit statement in ODBC-DSN does not work?

Schroeder

[Rick Brandt]

M. Schroeder wrote:

Does the Oracle table definition include a Primary Key or a Unique
Index? If not then when creating the link you should be getting
prompted for the fields that can be used to build a local unique
index. Without one of these three things an ODBC link will not be
editable.

(There was index and PK created, but I got nor prompt on creating the
link) But I took another table, with no primary key and no index.
Now I can insert records, but after closing the form they are not
persistent.
On re-opening the form the records are gone.

Looks to me as if the commit statement in ODBC-DSN does not work?

Schroeder

Sounds like you set the DatEntry property of the form to Yes. In that mode it
is only intended for doing new insertions and does not display any existing
records. They are still in the table though.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[M. Schroeder]

"Rick Brandt" <ri*********@hotmail.com> schrieb im Newsbeitrag
news:35*************@individual.net...

M. Schroeder wrote:

Does the Oracle table definition include a Primary Key or a Unique
Index? If not then when creating the link you should be getting
prompted for the fields that can be used to build a local unique
index. Without one of these three things an ODBC link will not be
editable.
(There was index and PK created, but I got nor prompt on creating the
link) But I took another table, with no primary key and no index.
Now I can insert records, but after closing the form they are not
persistent.
On re-opening the form the records are gone.

Looks to me as if the commit statement in ODBC-DSN does not work?

Schroeder

Sounds like you set the DatEntry property of the form to Yes. In that

mode it is only intended for doing new insertions and does not display any existing records. They are still in the table though.

Yes - I had DatEntry on Yes and re-set it (does not sound very logical to
me), and now it works well on my test table. I can do updates and inserts.

But the "real prod-table" does not work with the same properties.

I did not delete the PK on my prod-table, I set it to inactive. Maybe thats
the problem and I have to delete it. But how can I get asked from Access to
build a new table with my key-constraints in Access? It would be nice to
keep the PK, because its not just a data-import where I can drop the Key and
rebuild it after the import.

Thanks,
Schroeder

[Rick Brandt]

M. Schroeder wrote:

Yes - I had DatEntry on Yes and re-set it (does not sound very
logical to me), and now it works well on my test table. I can do
updates and inserts.

But the "real prod-table" does not work with the same properties.

I did not delete the PK on my prod-table, I set it to inactive. Maybe
thats the problem and I have to delete it. But how can I get asked
from Access to build a new table with my key-constraints in Access?
It would be nice to keep the PK, because its not just a data-import
where I can drop the Key and rebuild it after the import.

Can you edit the linked table directly (without using your form)?

The "normal" process is that when you initially create the link to an ODBC table
Access will either detect a Primary Key or Unique Index on the server and
utilize it. If it does not it will prompt you to choose fields for building a
local index.

Were you so prompted when you created the link? If you were prompted and
selected a field or fields to use as a local index did you ever refresh the link
after that? This will cause the local index to be lost. To get it back you
would need to delete the link and then recreate it.

If you are not prompted to choose fields when creating the link then that should
mean that Access is already picking up an index from the server in which case
the link should be editable. Are you sure your permissions on the server allow
for editing?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[M. Schroeder]

"Rick Brandt" <ri*********@hotmail.com> schrieb im Newsbeitrag
news:35*************@individual.net...

M. Schroeder wrote:

Yes - I had DatEntry on Yes and re-set it (does not sound very
logical to me), and now it works well on my test table. I can do
updates and inserts.

But the "real prod-table" does not work with the same properties.

I did not delete the PK on my prod-table, I set it to inactive. Maybe
thats the problem and I have to delete it. But how can I get asked
from Access to build a new table with my key-constraints in Access?
It would be nice to keep the PK, because its not just a data-import
where I can drop the Key and rebuild it after the import.

Can you edit the linked table directly (without using your form)?

The "normal" process is that when you initially create the link to an ODBC

table Access will either detect a Primary Key or Unique Index on the server and
utilize it. If it does not it will prompt you to choose fields for building a local index.

Were you so prompted when you created the link? If you were prompted and
selected a field or fields to use as a local index did you ever refresh the link after that? This will cause the local index to be lost. To get it back you would need to delete the link and then recreate it.

If you are not prompted to choose fields when creating the link then that should mean that Access is already picking up an index from the server in which case the link should be editable. Are you sure your permissions on the server allow for editing?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

I just deleted the link, deleted the primary key in Oracle and buildt the
link new - then choosed a composed primary key in Access. (Editing of the
former key was not possible.)
But now it works fine; I think I can shift constraints from Oracle to
Access.

Thanks,
Schroeder