Writing into external table not possible

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

re: Writing into external table not possible

M. Schroeder wrote:[color=blue]
> 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[/color]

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

re: Writing into external table not possible

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

(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

re: Writing into external table not possible

M. Schroeder wrote:[color=blue][color=green]
>> 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.[/color]
>
> (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[/color]

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

re: Writing into external table not possible

"Rick Brandt" <rickbrandt2@hotmail.com> schrieb im Newsbeitrag
news:35fatpF4maiu6U1@individual.net...[color=blue]
> M. Schroeder wrote:[color=green][color=darkred]
> >> 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.[/color]
> >
> > (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[/color]
>
> Sounds like you set the DatEntry property of the form to Yes. In that[/color]
mode it[color=blue]
> is only intended for doing new insertions and does not display any[/color]
existing[color=blue]
> records. They are still in the table though.[/color]

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

re: Writing into external table not possible

M. Schroeder wrote:
[color=blue]
> 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.[/color]


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

re: Writing into external table not possible

"Rick Brandt" <rickbrandt2@hotmail.com> schrieb im Newsbeitrag
news:35ff0qF4kjie5U1@individual.net...[color=blue]
> M. Schroeder wrote:
>[color=green]
> > 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.[/color]
>
>
> 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[/color]
table[color=blue]
> 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[/color]
building a[color=blue]
> 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[/color]
the link[color=blue]
> after that? This will cause the local index to be lost. To get it back[/color]
you[color=blue]
> would need to delete the link and then recreate it.
>
> If you are not prompted to choose fields when creating the link then that[/color]
should[color=blue]
> mean that Access is already picking up an index from the server in which[/color]
case[color=blue]
> the link should be editable. Are you sure your permissions on the server[/color]
allow[color=blue]
> for editing?
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>[/color]

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