Greg_Beam
asked on
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.
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
what error do you get
ASKER
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
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
what if you do this:
If Lrs.EOF and Lrs.BOF Then
AssignNextVal = 0
Else
AssignNextVal = Lrs.Fields("CONSOLIDATED_MAPPING_ID").Value
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
you cannot avoid the password, unless you can use windows authentication.
http://msdn.microsoft.com/en-us/library/dd788095(BTS.10).aspx
http://msdn.microsoft.com/en-us/library/dd788095(BTS.10).aspx
ASKER
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!
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!