Call Oracle Stored Procedure from Access

[Jeremy Ellison]

I am lost...I am new to all of this, so hopefully I can ask the question well
enough for you folks to help. I have searched all the related articles and
internet for the past three days, but I just can't seem to adapt any code to
work for me.

I am working on an access .mdb. I am creating a custom "sign on" form where
the users need to enter their username in one field and a password in another
field. They will then click "OK" and I want it to check if their username
and password is correct...if it is I want them to enter the database, else
they are denied.

Because the company I work for already has a large database system which
everyone has a username and password for, I want mine to go out and check
that same password and user name.... I have spoken with our IT folks and
they told me they accomplish thsi verification using a stored procedure on an
oracle database. The stored procedure name is:
permanent_report.rmsutils.logonuser

Apparently they use the following code in their program (Delphi ???) to
check the username/password and return whether it is valid and some other
information:

cstmt =
conn.prepareCall("call
permanent_report.rmsutils.logonuser@gis_rms.OurCompany.city ( " +
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");


cstmt.setString(1, agency.trim());
cstmt.setString(2, userName.trim());
cstmt.setString(3, password.trim());
cstmt.setString(10, "F"); // Log Session
cstmt.registerOutParameter ( 4, java.sql.Types.VARCHAR );
// User_Id
cstmt.registerOutParameter ( 5, java.sql.Types.VARCHAR );
// Result
cstmt.registerOutParameter ( 6, java.sql.Types.VARCHAR );
// IsAdmin?
cstmt.registerOutParameter ( 7, java.sql.Types.VARCHAR );
// Last Name
cstmt.registerOutParameter ( 8, java.sql.Types.VARCHAR );
// First Name
cstmt.registerOutParameter ( 9, java.sql.Types.VARCHAR );
// Unit

cstmt.execute();


-------------------------------
SO -->

I have tried to adapt so many different pieces of code and been
unsuccessful... I don't have a clue what I am doing wrong, the error
messages are not making sense...I am hopeing someone can guide me down the
right path...I have tried code within my form that is executed with the
command button, cmdLogon, I have just experimented with a "pass - through
query" and I can't get anything to work right.... arg

Thank you for your time...

 

[Jeremy Ellison]

WELL -----> Here is what I came up with for all those who might have to try
this in the future... this works great... thnx to our IT folks for their
advice:


============

Private Sub cmdLogon_Click()
On Error GoTo err_cmdLogon_Click

'CODE to verify User name and password
'general declaration
Dim CON As New ADODB.Connection
Dim PR As New ADODB.Parameter
Dim PR1 As New ADODB.Parameter

CON.Open "Provider=MSDAORA.1;Password=******;User ID=******;Data
Source=SPW;Persist Security Info=True"
Dim CMD As New ADODB.Command
CMD.ActiveConnection = CON
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "permanent_report.rmsutils.logonuser"

Set PR1 = CMD.CreateParameter("p_agency", adVarChar, adParamInput, 6, "SP")
Set PR2 = CMD.CreateParameter("p_logon", adVarChar, adParamInput, 6, "178600")
Set PR3 = CMD.CreateParameter("p_password", adVarChar, adParamInput, 15,
"eme001")
Set PR4 = CMD.CreateParameter("p_rms_user_id", adVarChar, adParamOutput, 15)
Set PR5 = CMD.CreateParameter("p_result", adVarChar, adParamOutput, 1)
Set PR6 = CMD.CreateParameter("p_admin", adVarChar, adParamOutput, 1)
Set PR7 = CMD.CreateParameter("p_ln", adVarChar, adParamOutput, 50)
Set PR8 = CMD.CreateParameter("p_fn", adVarChar, adParamOutput, 50)
Set PR9 = CMD.CreateParameter("p_unit", adVarChar, adParamOutput, 50)
Set PR10 = CMD.CreateParameter("p_startlog", adVarChar, adParamInput, 1, "F")

CMD.Parameters.Append PR1
CMD.Parameters.Append PR2
CMD.Parameters.Append PR3
CMD.Parameters.Append PR4
CMD.Parameters.Append PR5
CMD.Parameters.Append PR6
CMD.Parameters.Append PR7
CMD.Parameters.Append PR8
CMD.Parameters.Append PR9
CMD.Parameters.Append PR10
CMD.Execute

MsgBox CMD.Parameters("P_Result")


If CMD.Parameters("p_result").Value = "Y" Then
MsgBox "Welcome " & CMD.Parameters("p_fn").Value & " " &
CMD.Parameters("p_ln").Value
Else
MsgBox "Invalid Username/Password"
End If
CMD.Cancel
CON.Close

exit_cmdLogon_Click:
Exit Sub

err_cmdLogon_Click:
MsgBox Err.Number & " " & Err.Description
Resume exit_cmdLogon_Click
End Sub