VB->Jet->ODBC->Oracle8i SQL select NextSequence Problem

I need to call an Oracle sequence from within my VB Code.
ODBC connection is used to connect to oracle database.
AFAIK the correct SQL Syntax for Oracle would be: (at least it works in sqlplus!)

Code:

---

SELECT MY_COUNTER.NEXTVAL as caid FROM dual;

---

"Caid" is nothing but a simple variable I use in vb code

VB Code:

Dim CAID as String
Dim sqlstring2 As String
sqlstring2 = "SELECT CAID_COUNTER.NEXTVAL as caid FROM dual"
Set rs2 = CurrentDb.OpenRecordset(sqlstring2)

produces an error:

"Error Code:3078
Error 3078 : The Microsoft Jet database engine cannot find the input table or query 'dual'. Make sure it exists and that its name is spelled correctly."
:eek2:


Anyone any idea how to get this sequence number via ODBC without VB or Oracle or Jet complaining? :confused:


Thx in advance
MIKQ

This is the assignment you need:

sqlstring2 = "SELECT CAID_COUNTER.NEXTVAL as " & caid & " FROM dual"

Thx for the try buddy, but still the same error message 3078 here.

I think "as caid" was never the probleme (not sure though) as Oracle uses this too for naming the column e.g.:

Code:

---

select currency as DOLLAR from world_currency_table
will give

DOLLAR
------
Eur
Dollar
Pound
Yen

Consequently <sqlstring2 = "SELECT CAID_COUNTER.NEXTVAL FROM dual"> gives the same error 3078

---

Somehow Jet/ODBC has a problem with Oracle sys.dual table which is a dummy table for everyone.
Have a look at:
Oracle Dummy Table
Jet/ODBC won't recognize it as a valid target for that select from somehow....
I think I need a way to work around that dual, or convince JET to allow that select.

Any ideas? :ehh: