Truncating an Oracle Table from Access 1

[djbjr]

Can anyone tell me how to truncate (delete data) a table in Oracle from Access?

I have a an Access table that will be loaded monthly into our Oracle database.

So far I have been able to load it with an append query but I cant figure out how to empty it from Access before I load it.

Any help will be greatly appreciated.

dj

 

[dhookom]

You could use a pass-through query from Access to execute a SQL query on your Oracle server.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tranman]

In general, to do things to an Oracle table, in a module, just make an oledb connection with appropriate uid/pwd, then do:
con.execute "<any SQL command>"
in this case,
con.execute "Delete from <tablename>"

In your case, if you just want to do a delete, you can just link the Oracle table (file|get external data|link tables, then do a delete query on the linked Access table.

Tranman

 

[djbjr]

Thanks Tranman....

FYI I tried to delete the linked table but it didnt work.

I will try the oledb suggestion and let you know how it works

dj

 

[Tranman]

Yo djbjr,
"I tried to delete the linked table but it didnt work"
Your SQL was like, "Delete from <Access tablename>", right?

You didn't try to delete (drop) the table?

I assumed you were trying to delete the rows, not the table itself...If you want to delete the Oracle table, you will need to do that through a connection object.

Tranman

 

[djbjr]

I am not dropping the table I am trying to delete the data and get the following error

Could not delete from specified tables. (Error 3086)

The help text told me that I may only have read only access but that is not the case. In fact it didnt even prompt me for me oracle user/pw.

 

[dhookom]

djbjr,
Do you expect us to know which of the several suggestions you have tried?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tranman]

djbjr,
If you are using the Oracle ODBC driver (I ALWAYS use the Microsoft ODBC driver for Oracle because it's WAY faster), then the ODBC datasource stores the userid (and maybe the password) from when you originally created the datasource.
Tranman

 

[djbjr]

dhookom

Thanks for the advice on the pass through query. I was able to figure out how to use it through the MS Access help text.

The pass through query worked like a champ and I was able to truncate the table.