ODBC Pass Through SQL to Oracle Express 10g

From: Greg Strong <news.REMOVE_at_geedubeeu.com.invalid>
Date: Mon, 03 Jul 2006 13:30:08 -0500
Message-ID: <monia2lil1ibappu1qd06ctti19borevnm@4ax.com>

Hello All,

I don't know if this is an appropriate topic here, but I'll ask the question. Maybe one of you can help. I've been banging my head on this one for a while.

I'm experimenting with using Access 2k2 as a front end to Oracle Express. I am creating 10 tables using pass through DDL queries. The PROBLEM is the general format of the VB code WORKS on the 1st 6 tables created, then an error is thrown which I can't find much help on. I've compared the code on the 7th table to earlier ones which are successful, and the general format is the same. It seems the more you try, the quicker the problem is created. Re-booting fixes the problem, but the error always 1st occurs on the 7th table with subsequent tries occurring quicker. On subsequent tries I always drop the tables with cascading constraints and a purge. The specifics are below. Any ideals?

Thanks!

,----- [ Error Message: ]
| Run-time error '3003':
| Could not start transaction; too many transactions already nested. `-----

,----- [ VB code on error: ]
| Set qdfPassThrew = dbCur.CreateQueryDef("") `-----

,----- [ ODBC driver: ]
| I'm using the ODBC driver that came with Oracle Database 10g Express | Edition, sqora32.dll, Oracle in XE.
`-----

VB Code on 6th table:

=====================>Begin Code>===================================>

Sub Create_tblRevenueCurYr()

    Dim wsCur As DAO.Workspace
    Dim dbCur As DAO.Database
    Dim qdfPassThrew As DAO.QueryDef
    Dim strSQL As String     

    Set wsCur = DBEngine.Workspaces(0)
    Set dbCur = wsCur.Databases(0)     

    strSQL = "CREATE TABLE sc.tblRevenueCurYr " & _

"( " & _
"InvcNo int " & _
"CONSTRAINT PK_InvcNo_RevCY PRIMARY KEY, " & _
"OrderNo int NOT NULL, " & _
"PdInvc char(6) NOT NULL, " & _
"InvcAmt number(21,0) NOT NULL, " & _
"InvcCGS number(21,0) NOT NULL " & _
");"

' Debug.Print strSQL

    'create query
    Set qdfPassThrew = dbCur.CreateQueryDef("")

    qdfPassThrew.Connect = "ODBC;" & ConnectSC()
    qdfPassThrew.SQL = strSQL
    qdfPassThrew.ReturnsRecords = False

    wsCur.BeginTrans
    qdfPassThrew.Execute
    Set qdfPassThrew = Nothing
    Set dbCur = Nothing
    Set wsCur = Nothing
End Sub

=====================<End Code<=====================================<

Thanks again!

-- 
Best Regards,
Greg   


-- 
Regards,

Greg Strong

Received on Mon Jul 03 2006 - 13:30:08 CDT