getting nextval from Oracle sequence

I am trying to obtain nextval from my Oracle sequence.  My ODBC driver is Oracle in OraHome92, not Microsoft ODBC for Oracle for which the attached code snippet was written.  I have tried to modify to use the driver I need.
Basically, my first choice would be to use the dsn I created via the Control Panel's ODBC Data Source Administrator as it references the TNS service name and does not store the user id or password.
I have created a pass-through query via QueryDesigner where I can even specify the ds name used by all the other linked tables, but I can't figure out how to use the query to get the value inot a text field so that when the insert to Oracle occurs, there will be a value for the tables PK.
Thanks for any help.

Option Compare Database
Function AssignNextVal() As Long
    Dim db As Database
    Dim LPassThrough As QueryDef
    Dim Lrs As DAO.Recordset
    Dim LSQL As String
 
    On Error GoTo Err_Execute
    Set db = CurrentDb()
       Set LPassThrough = db.CreateQueryDef("qryTemp")
     
    LPassThrough.Connect = "ODBC;Driver={Oracle in OraHome92};Dbq=ECODS_Product_Admin;Uid=*****;Pwd=*****"
    LPassThrough.SQL = "Select STG_XLS.SEQ_CONSOLIDATED_MAPPING_KEY.nextval as CONSOLIDATED_MAPPING_ID From Dual"
    LPassThrough.ReturnsRecords = True
 
    Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)
 
    'Retrieve NextVal from Oracle sequence
    If Lrs.EOF = False Then
        AssignNextVal = Lrs("NV")
    Else
        AssignNextVal = 0
    End If
 
    'Remove query definition when done
    CurrentDb.QueryDefs.Delete "qryTemp"
 
    Exit Function
 
Err_Execute:
 
    'Remove query definition when done
    CurrentDb.QueryDefs.Delete "qryTemp"
 
    'Return 0 if an error occurred
    AssignNextVal = 0
 
End Function

Open in new window

Greg_BeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what error do you get
0
Greg_BeamAuthor Commented:
The error message is "Runtime error '3146'  ODBC-call failed"

What do you think of this approach?  I would like to not include my password.  

Why do some people wnat to avoid a dsn?  

Thanks,
Greg
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if you do this:
If Lrs.EOF and Lrs.BOF Then
  AssignNextVal = 0
Else
  AssignNextVal = Lrs.Fields("CONSOLIDATED_MAPPING_ID").Value
End If

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
anyhow, if you create the querydef just to delete it afterwards, I would do it a less intensive method
Option Compare Database
Function AssignNextVal() As Long
    Dim db As ADODB.Connection 
    Dim Lrs As ADODB.Recordset
    Dim LSQL As String
 
    On Error GoTo Err_Execute
    Set db = new adodb.connection     
    db.open "Driver={Oracle in OraHome92};Dbq=ECODS_Product_Admin;Uid=*****;Pwd=*****"
 
    LSQL = "Select STG_XLS.SEQ_CONSOLIDATED_MAPPING_KEY.nextval as CONSOLIDATED_MAPPING_ID From Dual"
 
    Set Lrs = db.execute(LSQL)
 
    'Retrieve NextVal from Oracle sequence
    If Lrs.EOF and lrs.bof Then
        AssignNextVal = 0
    Else
        AssignNextVal = Lrs("CONSOLIDATED_MAPPING_ID").Value
    End If
    Lrs.Close
    db.Close
 
    Exit Function
 
Err_Execute:
 
    'Return 0 if an error occurred
    AssignNextVal = 0
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Greg_BeamAuthor Commented:
Yes!!! That works!

May ask how I could avoid using my id and password, that is, use the existing ODBC connection instead of creating a new one?

Greg
0
Greg_BeamAuthor Commented:
angelii,
I am very have to have a solution, I know nothing about this area.  But I would still prefer a solution that does not involve hard-coding a password.
Thanks again,
Greg
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot avoid the password, unless you can use windows authentication.
http://msdn.microsoft.com/en-us/library/dd788095(BTS.10).aspx
0
Greg_BeamAuthor Commented:
Ok, thanks.

Also, I put the function as the default value of the field containing the PK.  So the combo box, I think, on the form presents the existing Id from existing rows, but calls the function for new rows that do not yet an Id assigned.  I was looking for an event to trigger calliing the function, and didn't find a good one.   This works well.

I am a first-time user of EE and I think it is very nice!  Thanks again angelll!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.