ADO: Mistakenly updating actual database by updating a recordset

[Guest]

Hello,

Using the code below based on a select query, I have been trying to loop through a recordset, while peforming the methods AddNew or Delete. However, in the AddNew case, it only goes through the loop once and adds one record before stoppiong. In the Delete case, I get a runtime error and cannot delete. This despite running Supports queries that verify that these methods are supported.

But then I find that my simple playing with the recordset has been updating the actual database!!! The SELECT query statement was taken from a query I actually saved in my Access database, and though I thought I was just playing with the recordset when I updated am actual field in the saved query!!! It also updated an actual field in one of the tables on which I based my SELECT query--that is, the query was based on joining existing tables, but I would have expected only the recordset, separate from the original tables, to be affected. Am I misunderstanding something here? Isn't the recordset independent of the tables on which it is based? (code follows below)

Richard

=====================

Dim myRecordSet, myRecordSetNew As ADODB.Recordset
Set myRecordSet = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT chorizon.hzname, chorizon.hzdepb_r, " & _
"chorizon.sandtotal_r, chorizon.silttotal_r, " & _
"chorizon.claytotal_r, chorizon.ksat_r, chorizon.awc_r, " & _
"chorizon.wtenthbar_r, chorizon.wthirdbar_r, " & _
"chorizon.wfifteenbar_r, chorizon.wsatiated_r, " & _
"chorizon.chkey, chtexturegrp.texture, " & _
"chtexturegrp.stratextsflag, chtexturegrp.rvindicator, " & _
"chtexturegrp.chtgkey, component.comppct_r, component.compname, " & _
"component.compkind, component.majcompflag, component.cokey, " & _
"mapunit.musym, mapunit.muname, mapunit.mukind, mapunit.muacres, " & _
"mapunit.mukey " & _
"FROM (mapunit INNER JOIN (component INNER JOIN chorizon " & _
"ON component.cokey = chorizon.cokey) " & _
"ON mapunit.mukey = component.mukey) INNER JOIN chtexturegrp " & _
"ON chorizon.chkey = chtexturegrp.chkey"

myRecordSet.Open strSQL, CurrentProject.Connection, adOpenStatic, _
adLockOptimistic, adCmdText

'Supports queries
If myRecordSet.Supports(adDelete) Then
Debug.Print "Supports Delete"
Else
Debug.Print "Doesn't Support Delete"
End If
If myRecordSet.Supports(adAddNew) Then
Debug.Print "Supports AddNew"
Else
Debug.Print "Doesn't Support AddNew"
End If
If myRecordSet.Supports(adFind) Then
Debug.Print "Supports Find"
Else
Debug.Print "Doesn't Support Find"
End If
If myRecordSet.Supports(adIndex) Then
Debug.Print "Supports Index"
Else
Debug.Print "Doesn't Support Index"
End If

On Error GoTo HandleErrors

With myRecordSet
.MoveFirst
Do
'.AddNew
.Fields("musym") = "NewSym"
'.Fields("mustatus") = "Newst"
.Update
Debug.Print .Fields("musym").Value
.MoveNext
Loop Until .EOF
Debug.Print .RecordCount
End With

Exit Sub

 

[Dan]

Good afternoon!

The recordset is not independent of the tables. Whatever
you select in the SQL statements is what the code will
affect. I believe that in ADO you can create a recordset
in memory that is not bound to any table, but that is
different from what your code is doing here. Your object
variables are pointing to actual tables in your database
and the code is making changes to the records in those
tables. Hope this helps!

Dan

-----Original Message-----
Hello,

Using the code below based on a select query, I have been

trying to loop through a recordset, while peforming the
methods AddNew or Delete. However, in the AddNew case, it
only goes through the loop once and adds one record
before stoppiong. In the Delete case, I get a runtime
error and cannot delete. This despite running Supports
queries that verify that these methods are supported.

But then I find that my simple playing with the recordset

has been updating the actual database!!! The SELECT query
statement was taken from a query I actually saved in my
Access database, and though I thought I was just playing
with the recordset when I updated am actual field in the
saved query!!! It also updated an actual field in one of
the tables on which I based my SELECT query--that is, the
query was based on joining existing tables, but I would
have expected only the recordset, separate from the
original tables, to be affected. Am I misunderstanding
something here? Isn't the recordset independent of the
tables on which it is based? (code follows below)

Richard

=====================

Dim myRecordSet, myRecordSetNew As ADODB.Recordset
Set myRecordSet = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT chorizon.hzname, chorizon.hzdepb_r, " & _
"chorizon.sandtotal_r, chorizon.silttotal_r, " & _
"chorizon.claytotal_r, chorizon.ksat_r, chorizon.awc_r, " & _
"chorizon.wtenthbar_r, chorizon.wthirdbar_r, " & _
"chorizon.wfifteenbar_r, chorizon.wsatiated_r, " & _
"chorizon.chkey, chtexturegrp.texture, " & _
"chtexturegrp.stratextsflag,

chtexturegrp.rvindicator, " & _

 

[Guest]

Thanks for responding Dan. I suppose you are right. But I think I have a good manual (Litwin et al. 2001), and that does not seem at all clear to me

Richar


----- Dan wrote: ----

Good afternoon

The recordset is not independent of the tables. Whatever
you select in the SQL statements is what the code will
affect. I believe that in ADO you can create a recordset
in memory that is not bound to any table, but that is
different from what your code is doing here. Your object
variables are pointing to actual tables in your database
and the code is making changes to the records in those
tables. Hope this helps

Da

-----Original Message----
Hello

trying to loop through a recordset, while peforming the
methods AddNew or Delete. However, in the AddNew case, it
only goes through the loop once and adds one record
before stoppiong. In the Delete case, I get a runtime
error and cannot delete. This despite running Supports
queries that verify that these methods are supported.has been updating the actual database!!! The SELECT query
statement was taken from a query I actually saved in my
Access database, and though I thought I was just playing
with the recordset when I updated am actual field in the
saved query!!! It also updated an actual field in one of
the tables on which I based my SELECT query--that is, the
query was based on joining existing tables, but I would
have expected only the recordset, separate from the
original tables, to be affected. Am I misunderstanding
something here? Isn't the recordset independent of the
tables on which it is based? (code follows below

 

[John Vinson]

Am I misunderstanding something here?
Yes.

Isn't the recordset independent of the tables on which it is based?

No. It's like an updateable View.