Oracle rollback in VBA

[Erik]

Hi,

I'm doing a project involving a Oracle database with an MS Access
application as front-end written in VBA.
I want to use specific Oracle calls like rollback and commit within
this application. I know I can use passthrough queries for this, but if
I define one with SQL property 'rollback' it produces an Oracle
failure.

my code:

Public Sub Rollback()
Dim psq As QueryDef
Dim strsql As String

Set psq = CurrentDb.CreateQueryDef("passthrough")
psq.Connect = globalConnectString
strsql = "rollback;"
psq.SQL = strsql
psq.ReturnsRecords = False
psq.Execute
psq.Close

End Sub

thanks in advance.

greetings,
Erik.

p.s. the globalconnectstring connects to oracle (this works in other
places).

 

[Erik]

Ok,

now that the rollback command is in capitals it does't produce any
failure anymore, however it also doesn't rollback.

just to let you know.

greetings,
Erik.

 

[TC]

I haven't done Oracle for yonks, but:

Set psq = CurrentDb.CreateQueryDef("passthrough")

1. Ideally you should not use currentb like that. You should cache it
in a variable & use it from there:

dim db as database
set db = currentdb()
(then use db in place of currentdb)

2. Are you sure that you create a passthrough query like that? I don't
have Access here to check. I bet it's a second or subseuent parameter
to createquerydef. Go back to your online help (or whatevr) and check
the syntax again.

Finally, are you certain that ROLLBACK is an Oracle SQL statement? I
though it was a PL/SQL statement. Maybe re-check that as well.

HTH,
TC