Pass Through DDL SQL to Oracle Express via ODBC - Run-time Error '3003'

[Greg Strong]

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 6th table to the 5th which is 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 6th table created at first 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:
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

--
Regards,

Greg Strong

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't have a CommitTrans. Change to this:

....
wsCur.BeginTrans
qdfPassThrew.Execute
wsCur.CommitTrans
Set qdfPassThrew = Nothing
....

You actually don't need the Transactions since you're doing pass thru
queries for one command, Transactions are usually for 2, or more,
commands. Anyway, the best place to do the transaction is on the Oracle
server using Oracle transaction commands.

You can also save time by creating a pass-thru QueryDef by hand and just
change the SQL property before running it. E.g.:

Sub RunQuery(strSQL As String, strQuery As String)
'In:
' strSQL The command to run
' strQuery The pass-thru query to use
'
CurrentDb.QueryDefs(strQuery).SQL = strSQL

CurrentDb.QueryDefs(strQuery).Execute

End Sub

Add your own error traps.

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKmCUYechKqOuFEgEQKBYwCghRE4lzf4SbFT0KheRCghWT vxMkkAni4k
isXmTr6j9r+DeS59/jp8ThOs
=g5rB
-----END PGP SIGNATURE-----
Greg Strong wrote:

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 6th table to the 5th which is 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 6th table created at first 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:
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

[Greg Strong]

On Mon, 03 Jul 2006 20:47:19 GMT, MGFoster <[e-mail]:

>You don't have a CommitTrans. Change to this:

Yep my mistake. I'm learning!!! :) Thanks!

--
Regards,

Greg Strong