Re: SQLPassthrough from Visual Basic to Oracle. HOW?

For some reason the pass through queries do not access DUAL!

Here is a quick and dirty way to achieve what you want.

Create a stored procedure on oracle ( or you can even write it as a PL/SQL block and pass it as a pass through statement!). All this procedure does is it will insert the next seq number into a temporary work table. Then query it using a record set. If lot of people need to get the next seq number, then identify each seq number by passing a random number. Once you get the next seq number into VB application delete that row from the work table.

Create PROCEDURE GetNextSeq(ID varchar2) IS

    NextSeqNum number(8);
BEGIN
    select ih_id.nextval.nextval into NextSeqNum from dual;     insert into work_tbl values(id, NextSeqNum); END;

-- 

Hope this works!

Sreeni

========================================
Sreeni Karpurapu 
Oracle, SAP DBA
Miracle Software Systems
Southfield, MI 48034
========================================

Northern Lake Service <norlake_at_newnorth.net> wrote in article
<34326F2D.C873FE3E_at_newnorth.net>...


> 

> 

> Mike Hackett wrote:

> 

> > I'm assuming that if you can attach in Access, you've already got

 SQL*Net


> > and ODBC drivers configured correctly.

> >

> 

> SQL*Net and ODBC are working correctly, but I cannot get the following

 code to


> work:

> 

> Dim Rs As Recordset, Db As Database, Ndb As Workspace

> Set Ndb = Workspaces(0)

> Set Db = Ndb.OpenDatabase("NLS", False, False,

> "odbc;dsn=NLS;uid=aspen;pwd=scattner;database=NLS")

> Set Rs = Db.OpenRecordset("select ih_id.nextval from dual;",

 dbOpenSnapshot,


> dbSQLPassThrough)

> Rs.Close

> 

> The above gives me  ------   "Run-time error '3146':   ODBC-call failed."

> 

> I have NLS in the ODBC Administrator, and the ODBC Test on it works fine.

> 

> I've tried the CreateQueryDef too, but must be doing it wrong.

> 

> Any ideas on what i'm doing wrong???

> 

> Chris Geske

> LIMS Manager

> Northern Lake Service, Inc.

> email:  norlake_at_newnorth.net

> 

>