how do I use access to delete data from a linked oracle table?

[Guest]

I have an access 2003 database with a link to an oracle table. I want to use
access to edit the data in the oracle table. how do I get access to delete
the data?

 

[Arvin Meyer [MVP]]

If the table is linked all you need is permissions in Oracle to edit/delete
the data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I have the relevent permissions.
Using a PL/SQL editor I can simply delete the data.
When viewing the data in Access 2003 I can open the table, select all the
fields but when I come to click delete it only deletes around half of the
data and then I get an error message.
When I tried deleting the data using a built in SQL query (in access 2003)
it says it has run ok but doesn't actually delete anything from oracle.
any ideas on why it isn't working properly?

 

[aaron.kempf]

MDB is too flaky to use in a real-world case like this.

Get rid of Oracle; use SQL Server. Access Data Projects are an awesome
alternative to ODBC flakiness.

-Aaron
ADP Nationalist

 

[Pieter Wijnen]

might be a problem with the ODBC driver and or the setup thereof
Are you using MS or Oracle ODBC, and what versions are involved?
GUI deletes tend to fail due to overhead anyway, always use Queries or
Recordsets to Delete multiple records

Pieter

 

[Guest]

How do I tell which driver I am using? When I went to link the tables it just
worked.

 

[Guest]

The error i keep getting is that a delete query can only delete one row/a
single record at a time. I need to delete all rows in the table and then
upload new data into the table. (around 6000 rows)

 

[Douglas J. Steele]

Delete queries should be able to delete as many rows as you want: DELETE
FROM table_name (or DELETE * FROM table_name)

However, I would think that Oracle would have something similar to SQL
Server's TRUNCATE that will empty a table.

In either case, you'll probably find it much faster if you use a
pass-through query, rather than deleting from the linked table.

 

[Pieter Wijnen]

TRUNCATE TABLE <TableName> Does indeed work Quick & efficient
I don't reccomend unexperienced users to use it though, as data is "lost"
(no recovery data logged) , no way to recover data since last back up
(well, not entirely true, but the cost might be pretty steep)

The version info etc you get from running ODBCAD32.exe (start /run) or
Locate it under Admin Tools inb Control Panel

Pieter
Oracle Professional DBA

 

[aaron.kempf]

or of course

if you want to avoid all the complexity of sql-passthrough.. then get
rid of oracle and MDB-- use SQL Server and ADP.

it is a much more elegant solution for reportign and data entry.

MDB just randomly CRAPS OUT and it can't be trusted with your data.

if you care enough to build a database; use an engine with future.

Oracle has been shedding market share for a decade.

and MDB is just plain STUPID.

 

[Guest]

thanks for the help.
after much trial and error it turns out the issue I had was that I did not
have a unique index and access stopped the delete query. I have added in a
new index field and the delete query now works