how to send truncate table command to oracle using passthrough query

[notageek7]

I'm having problems with using the truncate command in a pass through query in access, it dosn't seem to like it. I've got a pass through query that sends the truncate command to delete values from an oracle database. Here is some of the code. Anybody done anything like this before? Thanks for your help in advance.


Set daoDatabase = CurrentDb
Set daoQueryDef = daoDatabase.CreateQueryDef("", sqlString)
daoQueryDef.Execute
Call CloseADOObject(daoQueryDef)

the sqlString is:
TRUNCATE TABLE LOAD_OPPORTUNITIES;

 

[fredericofonseca]

And what is the error message you get?

And why don't you connect directly to Oracle instead of using Access for that type of things?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

And regarding the original problem.


1- What Oracle version
2- Does the user used to connect to Oracle (on the passthrough definition) has accees "DROP ANY TABLE system privilege"?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[notageek7]

Yes fredericofonseca the privileges are okay. This application has been an ongoing project over the last several years, I have been working with it for six months now. It was originally all access/vba now it has an oracle 9i backend. So needless to say there have been many issues that needed to be resolved and others that still need to be tackled.

 

[fredericofonseca]

Can you show us the error you get and the connection string used to connect to Oracle please.

Have you checked to see if the particular user used on the above connection can execute that SQL on SQL*Plus?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[notageek7]

fredericofonseca thank you for your time....I got it to work by using:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qrypassthroughTEST"
DoCmd.SetWarnings True

 

[fredericofonseca]

Ok. Good to know it's fixed.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[notageek7]

got pass through query to work in code but now trying to get the same query to work as originally attempted...as per my first comments above....

this code creates the error below:
Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NewQuery", _
"TRUNCATE TABLE sfdceim.sfdccms_region")
dbs.QueryDefs.Append qdf


Run-time error '3129:
expected 'DELETE', 'INSERT',....

 

[fredericofonseca]

You are getting that error because the query you are creating is NOT a pass through query.
Access does not have a TRUNCATE verb.

try

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NewQuery", _
"TRUNCATE TABLE sfdceim.sfdccms_region")
qdf.type = dbQSQLPassThrough
qdf.connect = "your_connection_string_to_oracle"

Other properties may be required.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[notageek7]

thanks for your help again fredericofonseca but i'm getting an error...."can't assign to read only property" for the line that is trying to define the type

qdf.type=dbQSQLPassThrough