Preserve on Commit

I want to process recordset and do periodic commits without losing the cursor.

I'm trying to use the "Preserve on Commit" property but I'm getting this msg:

ERROR DESCRIPTION: Item cannot be found in the collection corresponding to the requested name or ordinal .

Any ideas, anybody, anywhere

------------------------------------------------------------------

Global cnConnect As ADODB.Connection

Set cnOracle = New ADODB.Connection

strConnection = "DRIVER={Microsoft ODBC for Oracle};" _
& "UID=" & strUserID & ";" _
& "PWD=" & strPassword & ";" _
& "SERVER=" & strDatabase & ";"
cnOracle.Open strConnection

----------------------------------------------------------
sub main

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Properties("Preserve On Commit") = True

rs.Open ssql, cnConnect ' ssql = sql statement
-------------------------------------------------------

Answer this question

Preserve on Commit

spshah
No, this:
DRIVER={Microsoft ODBC for Oracle};
LuckyL
I guess it's easy enough to put the rs in it's own connection so it's not affected by the commit but I was just curious as to why the Preserve on Commit property isn't available.....I'll give OLEDB a shot....Thanks !
JamesCox1968
I'm fuzzy on the subject, especially where it deals with Oracle. However, ODBC is an old database interface technology. OLEDB is new and, I'm fairly sure, is the driver/provider that implements the "Preserve on Commit" proprty. I'm sure that an OLEDB provider for Oracle is available, I gave you the download URL. Judging from this website, the connection string should read something like "Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd". Since you already use "msdaora", maybe you already have the OLEDB provider installed. Give it a shot...
laiseng
That is the driver that I'm using...The Provider is a property of the connection, is it not

BTW - Thanks for your input :)
sunny123
Doubtful, it is listed as a property used by the Oracle data provider. I noticed in your connection string that you use the ODBC driver. Perhaps you probably need the OLEDB provider.
Vladimir Chtepa
Is this what you're referring to

With cnOracle
.Provider = "MSDAORA"
End With
Drozdi
Wild guess: try setting the property *after* opening the recordset.
Evan Mulawski
I had tried that but: "Operation not allowed while object is open".

We're Oracle..Could this be a Sql Server vs Oracle issue
Preserve on Commit

Answer this question