Hello,
Created an Oracle sequence to give me the next number, however, if i
run the command from my Oracle SQL it works fine, but if i run from
Excel it adds three to the next number. It keeps jumping by threes?

Here's all the code Oracle and Excel.

Oracle Sequence: done to create the Oracle sequence, run only 1 time.
Create sequence BRP_NextNum
start with 9900
increment by 1
nomaxvalue
minvalue 9900
nocycle
cache 50
noorder

Oracle SQL for nextval: Select BRP_NEXTNUM.NEXTVAL FROM DUAL; (this
works from My SQL Program!)

Excel Code: - this jumps the number by 3
Sheets("Sheet1").Select
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Kolum As Integer 'for entering headers
' Open the connection
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=****;" & _
"Uid=****;" & _
"Pwd=****;"

rst.Open _
"select BRP_NEXTNUM.NEXTVAL FROM DUAL ", _
cnn, adOpenKeyset

For Kolum = 0 To rst.Fields.Count - 1 'enter field headers
Cells(1, Kolum + 1) = rst.Fields(Kolum).Name
Next
Cells(2, 1).CopyFromRecordset rst 'dump the result to the sheet
'close the connection
rst.Close

thanks.

change the odbc drive to just the Oracle drive.
then use this code

Dim adoCN As Object
Dim adoRS As Object
Dim rngCell As Range
Dim lngX As Long


Set adoCN = VBA.CreateObject("ADODB.Connection")
adoCN.CursorLocation = 3
adoCN.ConnectionString = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=***;DBQ=***;UID=***;PWD=***;"
adoCN.CommandTimeout = 0 'NO TIMEOUT
adoCN.Open
Set adoRS = VBA.CreateObject("ADODB.Recordset")
adoRS.MaxRecords = 0 '0 = ALL RECORDS
Set adoRS = adoCN.Execute("select BRP_NEXTNUM.NEXTVAL FROM DUAL")

For lngX = 1 To adoRS.Fields.Count
ActiveCell.Offset(0, lngX - 1).Value = adoRS.Fields(lngX - 1).Name
Next lngX
ActiveCell.Offset(1, 0).Select

ActiveCell.CopyFromRecordset adoRS
adoRS.Close
Set adoRS = Nothing

works great Ron.
thanks for all your help!


wrote:
Hello,
Created an Oracle sequence to give me the next number, however, if i
run the command from my Oracle SQL it works fine, but if i run from
Excel it adds three to the next number. It keeps jumping by threes?

Here's all the code Oracle and Excel.

Oracle Sequence: done to create the Oracle sequence, run only 1 time.
Create sequence BRP_NextNum
start with 9900
increment by 1
nomaxvalue
minvalue 9900
nocycle
cache 50
noorder

Oracle SQL for nextval: Select BRP_NEXTNUM.NEXTVAL FROM DUAL; (this
works from My SQL Program!)

Excel Code: - this jumps the number by 3
Sheets("Sheet1").Select
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Kolum As Integer 'for entering headers
' Open the connection
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=****;" & _
"Uid=****;" & _
"Pwd=****;"

rst.Open _
"select BRP_NEXTNUM.NEXTVAL FROM DUAL ", _
cnn, adOpenKeyset

For Kolum = 0 To rst.Fields.Count - 1 'enter field headers
Cells(1, Kolum + 1) = rst.Fields(Kolum).Name
Next
Cells(2, 1).CopyFromRecordset rst 'dump the result to the sheet
'close the connection
rst.Close

thanks.