MS Access 2000 execute Oracle SQL via DAO

[SimonSellick]

I need to run an SQL ALTER USER command (no recordset returned) on an Oracle 9i database, from an Access 2000 application. All the existing tables are ODBC-linked tabledefs using DAO and this works.

I thought that the way to go would be to create an ODBC workspace, connect it to the Oracle db and use the .Execute method to run the SQL; but I can't get the connection to work. I'm using the same connection string as I already use on the linked tabledef.connection objects:

Dim w As Workspace
Set w = CreateWorkspace("A", "a", "a", dbUseODBC)
c = StrReplaceArgs(connect, "?", u, p, e)
w.OpenConnection "A", dbDriverComplete, , c 'xx fails here
w.Connections("A").Execute s ' this is the SQL
w.Connections("A").Close
w.Close

The connection string c looks like this:

ODBC;DRIVER={ORACLE ODBC DRIVER};" _
& "SERVER=xxxx;UID=xxxx;PWD=xxxx;DBQ=xxxx;" _
& "DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;" _
& "LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;" _
& "DATABASE="

Any ideas welcomed.

 

[fredericofonseca]

Please post the full error message.

Also you better use ADO with Oracle.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SimonSellick]

Hi Frederico,

It was the usual non-specific 'ODBC -- Call Failed' error.

You were right - it's much easier with ADO. I altered the project references and it worked first time. Thanks!

Simon.