ADO: Can't loop through Recordset if editing

[Richard]

This is the wierdest thing. If I try to Add a new record, I can't loop through the recordset. It only goes through once. If I try to delete a records, I get a runtime error. Yet, I have verified that the recordset supports both these methods (AddNew and Delete). If I simply loop through, printing dummy statements, it works fine, going through all the records. Any ideas what's going on? (Code follows below

Richar

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

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

Dim strSQL As Strin
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, adCmdTex

'Supports querie
If myRecordSet.Supports(adDelete) The
Debug.Print "Supports Delete
Els
Debug.Print "Doesn't Support Delete
End I
If myRecordSet.Supports(adAddNew) The
Debug.Print "Supports AddNew
Els
Debug.Print "Doesn't Support AddNew
End I
If myRecordSet.Supports(adFind) The
Debug.Print "Supports Find
Els
Debug.Print "Doesn't Support Find
End I
If myRecordSet.Supports(adIndex) The
Debug.Print "Supports Index
Els
Debug.Print "Doesn't Support Index
End I

On Error GoTo HandleError

With myRecordSe
.MoveFirs

D
.Delet
'AddNe
Debug.Print "I am here
'.Updat
.MoveNex

Loop Until .EO
Debug.Print .RecordCoun

End Wit

Exit Su

 

[John Spencer (MVP)]

As a guess, you are creating a non-updatable recordset.

If you just run the query as a query, is it updatable?

 

[Richard]

Thanks for responding, John

1. Something's fishy, because my Supports queries indicate that I should be able to Delete and AddNew, but immediately after this I fail to be able to do so. How could it be a non-updatable recordset if if the Supports queries say the recordset supports AddNew and Delete

2. Do you mean if I use Access menus/buttons to create/save the select query, then come back and try to update it, say by deleting a record under direct user control? That does work. If that's not what you meant, would you please clarify

Thanks
Richar

----- John Spencer (MVP) wrote: ----

As a guess, you are creating a non-updatable recordset

If you just run the query as a query, is it updatable?

Richard wrote

 

[John Spencer (MVP)]

That was what I meant. In this case I am stuck. Perhaps someone else sees a
problem that I am missing.

 

[Gary Walter]

Hi Richard,

A couple of trivial things bother me
that may have nothing to do your problem, but....

Dim myRecordSet, myRecordSetNew As ADODB.Recordset

I would think should be:

Dim myRecordSet As ADODB.Recordset, myRecordSetNew As ADODB.Recordset

-------------------
You appear to not be closing the recordset
at the end of the sub. Maybe you just did not
include that part of your code for brevity?

Hopefully at the end you are

myRecordSet.Close
Set myRecordSet = Nothing
--------------------
I cannot tell what CursorLocation is.

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

Could it be the default adUseServer
(unless Connection has been set to adUseClient)

What do you get if you add the following stmts
after you open the recordset:
(from http://support.microsoft.com/default.aspx?kbid=188857 )

Debug.Print "CursorLocation " & myRecordSet.CursorLocation
Debug.Print "CursorType " & myRecordSet.CursorType
Debug.Print "LockType " & myRecordSet.LockType

CursorLocation CursorType LockType
----------------------------------------------------------

0 N/A adforwardonly N/A
1 N/A adopenkeyset readonly
2 aduseserver adopendynamic pessimistic
3 aduseclient adopenstatic optimistic

..
What happens if you try:

With myRecordSet
.Source = strSQL
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open Options:=adCmdText
End With

Probably trivial, but had to respond.

Good luck,

Gary Walter

That was what I meant. In this case I am stuck. Perhaps someone else sees a
problem that I am missing.

to Delete and AddNew, but immediately after this I fail to be able to do so. How
could it be a non-updatable recordset if if the Supports queries say the recordset
supports AddNew and Delete?then come back and try to update it, say by deleting a record under direct user
control? That does work. If that's not what you meant, would you please clarify?through the recordset. It only goes through once. If I try to delete a records, I
get a runtime error. Yet, I have verified that the recordset supports both these
methods (AddNew and Delete). If I simply loop through, printing dummy statements, it
works fine, going through all the records. Any ideas what's going on? (Code follows
below)