How do you run Oracle's NEXTVAL and CURRVAL via ODBC connection

[Escolar]

I am running queries and updates against an Oracle Database using Access 97 connected via ODBC. This is a much friendlier environment than Oracle's SQL Plus

All goes well until I have to insert a new record using an Oracle "Sequence". This function is similar to AutoNumber in Access. But you have to call the function.
Example to get the next account number you call AcctNum.NEXTVAL. You can find the last used value by SELECT AcctNum.CURRVAL FROM ..

Neither the NEXTVAL or the CURRVAL functions work. B-(
I have tried SQLPassThrough, but no good either.
PLEASE HELP.

 

[B827]

Is this the same as running a stored procedure in Oracle? I have run PL-SQL procedures written by Oracle. The code I used was:

Function RunProcedure(Userid As String, Pwd As String, ProcName As String)
Dim ErrMsg As String, ConnectStr As String, ProcStr As String
ConnectStr = "ODBC;DSN=Sandy;UID=" & Chr(34) & Userid & Chr(34) & ";PWD=" & Chr(34) & Pwd & Chr(34) & ";SERVER=TNS:SCLPROD;"
On Error GoTo Err_RunProcedure
Dim wrkODBC As Workspace, qdf As QueryDef
Dim conODBC As Connection, Rst As Recordset, SQLStr As String
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "CRMLIVE", "CRMLIVE", dbUseODBC)
Set conODBC = wrkODBC.OpenConnection("Publishers", dbDriverNoPrompt + dbRunAsync, False, ConnectStr)
Do While conODBC.StillExecuting
If MsgBox("No connection yet--keep waiting?", vbYesNo) = vbNo Then ' If the connection has not been made, ask the user
conODBC.Cancel ' if he/she wants to keep waiting. If the user does not, cancel
MsgBox "Connection cancelled!" ' the connection and exit the procedure.
wrkODBC.Close
Exit Function
End If
Loop
ProcStr = "{CALL " & ProcName & "}"
Set qdf = conODBC.CreateQueryDef("qry", ProcStr)
qdf.Execute
qdf.Close
conODBC.Close
wrkODBC.Close
MsgBox "Procedure Run Complete", vbInformation + vbOKOnly, "System Update"
Exit_RunProcedure:
Exit Function

Err_RunProcedure:
ErrMsg = "Error Running Oracle Procedure" & vbCrLf & vbCrLf & "Error Code =" & Err.Number & vbCrLf & "Error Description =" & Err.Description
MsgBox ErrMsg, vbCritical + vbOKOnly, "Run Procedure Failure"
Resume Exit_RunProcedure
End Function

Hope this helps, if you do find a solution can you post it please? I often get asked to produce little front end systems, because, as you say it is much easier. Information like that is always useful.

Good Luck Sandy

 

[Escolar]

Thanks Sandy,
This looks like the sort of stuff I need. I'll give it a try within the next few days and keep you posted.
ESCOLAR

 

[Escolar]

Sandy,
Thanks for the help. I am close.
ACCT_SEQ is a defined Oracle Sequence on the database.
When I run the qdf.execute with "CALL ACCT_SEQ.NEXTVAL" I get this message:

"Error Code = 3667
Error description = "A different operation is preventing this operation from being executed."

Any ideas?

Also if I execute a simple command such as Select * from ACCOUNTS, I get the same error as above.
Note that all queries, updates etc work using Access Queries against this same database, it's only the NEXTVAL and CURRVAL which don't work.
ESCOLAR

 

[B827]

Escolar
It means nothing to me - but I'll ask around. Sandy

 

[B827]

Hi Escolar
my Oracle contact came back with:

Unfortunately this seems to be an error code applicable to Macintosh only according to the book I have, which means to get more information about the problem requires a Mac specific manual. I have checked on the Oracle support web site and there is no reference to this error number.

Let me know if the Oracle address would be of assistance, I'm stuck now.

Sorry about that.
[Sad] [Sad] Sandy

 

[Escolar]

Sandy,
I appreciate all your research. I am coping using a workaround to get past the problem at present. Macintosh - what's that - an English coat? I'm using Access 2000 on a Windows machine.
ESCOLAR

 

[B827]

Hi Escolar

I'd be interested if you solve the problem, I can't do anything because I don't own any Oracle systems. Could you let me know any useful information pleae?

By the way Macintosh = Scottish Raincoat!

All the best.

Sandy