How do I call an Oracle function from ACCESS97 VBA?

I am trying to use Microsoft Access to run an Oracle function, to retrieve a user's password. However, I am having no luck.

If I create a pass-through query in Access, with the ODBC Connect String property set correctly and with the exact same SQL, it works fine if I run it.

My sample code is below:

• This does not work ******************************
  ' Create a new connection to the Server
  Dim objConnect As New clsODBCDirect Dim strOraclePassword As String

With objConnect ' Create the connection to the Oracle Database If Not .OpenDatabase(DSN:="XXX", UID:="JoeBlow", PWD:="X", Database:="XXX") Then End If End With

Dim rst As Recordset
sSQL = "SELECT validate_user('JoeSmow') as password FROM DUAL" With objConnect

    Set rst = .Connection.OpenRecordset(sSQL, dbOpenDynamic, dbRunAsync, dbReadOnly)
End With

strOraclePassword = IIf(IsNull(rst!Password), Empty, rst!Password)

• This does work ******************************
  ' Create the SQL string to select UserName's Password.
  sSQL = "SELECT validate_user('JoeSmow') as password FROM DUAL" dbsIXC.QueryDefs!qryGetDefaultUserPassword.SQL = sSQL

' Run the query that will get the default user's password.
Set rst = dbsIXC.OpenRecordset("qryGetDefaultUserPassword") strOraclePassword = IIf(IsNull(rst!Password), Empty, rst!Password)

When I issue the OpenRecordset command, I get ODBC Call Failed, when I try the 1st set of code.

Has anyone had any success trying to use Access to call an Oracle Function, that could help me out?

I have tried using the Oracle TechNet web page to see if there is anything that could help me out, but I have had no luck.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 27 1998 - 17:02:25 CDT