?
Solved

getting nextval from Oracle sequence

Posted on 2009-07-15
8
Medium Priority
?
1,482 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Greg_Beam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24866788
what error do you get
0
 

Author Comment

by:Greg_Beam
ID: 24869398
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871647
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24871665
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
 

Author Comment

by:Greg_Beam
ID: 24872220
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
 

Author Closing Comment

by:Greg_Beam
ID: 31604069
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24876040
you cannot avoid the password, unless you can use windows authentication.
http://msdn.microsoft.com/en-us/library/dd788095(BTS.10).aspx
0
 

Author Comment

by:Greg_Beam
ID: 24877651
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question