Modifying a recordset

[Brian]

Hi!

I have a recordset that (after it is generated) needs to have several record
deleted. I have tried this using ADO but when i find the record to be
deleted and try to delete it, i get an error saying that the data is read
only. My code follows:

---------------------------------
Sub Form_Load()

Dim zipStore As String


On Error GoTo Err_Load
Dim rec As Boolean

Set rst = New ADODB.Recordset
Debug.Print "LOAD " & strsearch
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
Debug.Print "hello"
rst.Open strsearch, Options:=adCmdText

Set Me.Recordset = rst
If Box = True Then 'Box is global varible in another module

Do Until rst.EOF
If Me!CR = "C" Then
zipStore = Me!ZIPCODE

Do Until rst.EOF
If ((Me!ZIPCODE = zipStore) And (Me!CR = "C")) Or
((Me!ZIPCODE = zipStore) And (Me!CR = "B")) Then
rst.Delete
End If
rst.MoveNext
Loop


End If
rst.MoveNext


Loop

End If

Exit_Load:
Exit Sub

Err_Load:
MsgBox Err.Description
Resume Exit_Load

End Sub

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

In all examples where the records are editable, there is a constant query
statement after rst.Open, such as "Select * ftom tblTimeCard" or something
simular. In my case, I cannot do this, because my query changes based on
options i have on a form.

Maybe i am approaching this in the totally wrong way, but can anyone tell me
how to make the recordset records deletable?

Thanks for any help!

Brian Boykin

 

[Alex Dybenko]

Hi,
in general your code looks ok. Do your table tblTimeCard has a promary key?

 

[Brian]

Well, the tblTimeCard is from an example in an Access book that i have, just
to show how a query might look when used in the code. So there is no actual
tblTimeCard table in my database.

My code is susposed to look through a huge table and find zip codes that
match the one you are looking for. Then delete all the PO Boxes in that Zip
Code if it also finds City Routes in the Zip Code. But i can't just erase
them from the huge original table because some searches might also want all
routes, which would need to include the city routes and PO Boxes. I dunno if
that made any sense to anybody, but i'm just trying to explain.

My code will not let me erase the city routes and PO Boxes from my recordset
because it is "read only". I don't understand how to make the recordset
editable so i can delete the unwanted records.

 

[Randy Harris]

Hi,
in general your code looks ok. Do your table tblTimeCard has a promary key?

Your code looks peculiar to me. What does strsearch contain? What is the
purpose of using the Me.Recordset? Why is there one loop nested within
another?

 

[Alex Dybenko]

Hi Brian,
so when you try to delete a record with rst - what error you get?
how your table looks like exactly?
you can also try to delete using SQL:

CurrentProject.Connection.Execute "Delete * from MyTable Where ID=" & strID

 

[Brian]

Your code looks peculiar to me. What does strsearch contain? What is the
purpose of using the Me.Recordset? Why is there one loop nested within
another?

Hi Randy. My strsearch contains an SLQ statement that changes based on
options that the user selects in my search form. I will include the code
that builds the strsearch string below:

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

Function SearchZips(txtZipCode As String, chkAllZips As Boolean, chkBox As
Boolean, chkRural As Boolean, chkAR As Boolean, chkAB As Boolean) As String

SearchZips = "ZipCode " & txtZipCode & " Rural " & chkRural

'construct the query string--------
Dim strqry As String
Dim strlength As Integer

If chkBox = True Then
Box = True
End If

If chkBox = False Then
Box = False
End If


strqry = "SELECT STATISTICS.ZIPCODE, STATISTICS.CARRIER_ROUTE_ID"
strlength = Len(strqry)
Debug.Print strlength

If chkAR = True Then
strqry = strqry & ",
Sum([AR_CENTRAL]+[AR_CURB]+[AR_NDCBU]+[AR_OTHER]+[AR_FACILITY]+[AR_CONTRACT]+[AR_DETACHED]+[AR_NPU]) AS AR"
End If

If chkAB = True Then
strqry = strqry & ",
Sum([AB_CENTRAL]+[AB_CURB]+[AB_NDCBU]+[AB_OTHER]+[AB_FACILITY]+[AB_CONTRACT]+[AB_DETACHED]+[AB_NPU]) AS AB"
End If

If (chkAB = True) And (chkAR = True) Then
strqry = strqry & ", Sum ([AR_CENTRAL] + [AR_CURB] + [AR_NDCBU] +
[AR_OTHER] + [AR_FACILITY] + [AR_CONTRACT] + [AR_DETACHED] + [AR_NPU] +
[AB_CENTRAL] + [AB_CURB] + [AB_NDCBU] + [AB_OTHER] + [AB_FACILITY] +
[AB_CONTRACT] + [AB_DETACHED] + [AB_NPU]) AS TOTAL"

End If

strqry = strqry & ", STATISTICS.CR FROM STATISTICS GROUP BY
STATISTICS.ZIPCODE, STATISTICS.CARRIER_ROUTE_ID, STATISTICS.CR"

If chkAllZips = False Then
strqry = strqry & " HAVING (((STATISTICS.ZIPCODE)='" & txtZipCode &
"')"

If (chkRural = True) And (chkBox = False) Then
strqry = strqry & " AND (((STATISTICS.CARRIER_ROUTE_ID) Like
'r%') OR ((STATISTICS.CARRIER_ROUTE_ID) Like 'g%') OR
((STATISTICS.CARRIER_ROUTE_ID) Like 'h%'))"
End If

If (chkRural = False) And (chkBox = True) Then
strqry = strqry & " AND (((STATISTICS.CARRIER_ROUTE_ID) LIKE
'b%') OR ((STATISTICS.CARRIER_ROUTE_ID) Like 'c%'))"
End If

If (chkRural = True) And (chkBox = True) Then
strqry = strqry & " AND (((STATISTICS.CARRIER_ROUTE_ID) Like
'r%') OR ((STATISTICS.CARRIER_ROUTE_ID) LIKE 'g%') OR
((STATISTICS.CARRIER_ROUTE_ID) LIKE 'h%') OR ((STATISTICS.CARRIER_ROUTE_ID)
LIKE 'b%') OR ((STATISTICS.CARRIER_ROUTE_ID) Like 'c%'))"
End If

' If (chkRural = False) And (chkBox = False) Then
strqry = strqry & ")"
' End If

End If

If chkAllZips = True Then

If (chkRural = True) Or (chkBox = True) Then
strqry = strqry & " HAVING"
End If

If (chkRural = True) And (chkBox = False) Then
strqry = strqry & " (((STATISTICS.CARRIER_ROUTE_ID) Like 'r%')
OR ((STATISTICS.CARRIER_ROUTE_ID) Like 'g%') OR
((STATISTICS.CARRIER_ROUTE_ID) Like 'h%'))"
End If

If (chkRural = False) And (chkBox = True) Then
strqry = strqry & " (((STATISTICS.CARRIER_ROUTE_ID) LIKE 'b%'))"
End If

If (chkRural = True) And (chkBox = True) Then
strqry = strqry & " (((STATISTICS.CARRIER_ROUTE_ID) Like 'r%')
OR ((STATISTICS.CARRIER_ROUTE_ID) LIKE 'g%') OR
((STATISTICS.CARRIER_ROUTE_ID) LIKE 'h%') OR ((STATISTICS.CARRIER_ROUTE_ID)
LIKE 'b%'))"
End If


End If



Debug.Print strqry
SearchZips = strqry

End Function
---------------------------------

So basically, if i wrote out each combination on the SQL query, it would
take forever.

Me.Recordset is used to assign rst as the Recordset for the form i'm using
to display the results. Is it possible to just display it straight toa table?

The loops are kinda hard to explane, but here goes....
The first loop is activated only if the user is looking for PO Boxes in a
zip code. If they are, then the loop looks through the recordset to find any
zipcodes that contain City Routes (Me!CR = "C". CR is the column that
contains the route type: R, C, B, H, or G). If a C is found in the CR column
for a zip code, then the zipcode of the current record is stored in zipStore.
The next loop then searches for all records that have that zip code and
tries to delete those records that contain CR = C ot CR = B. The reason for
this is based on some crazy Postal rules.

But the SQL that is generated and stored in strstring works fine. My
problem is when the code come across rst.Delete. It says the recordset is
read-only and the records can not be deleted. I just want to know how to
delete these records.

Does that help any?

 

[Brian]

Hey Alex. I get a message that says the records a re read-only and can't be
changed.

The table my query generates has 6 columns: ZIPCODE, CARRIER_ROUTE_ID, AR,
AB, TOTAL, anc CR.

The table i'm running the querry on has 46 columns in it. If you really
need me to i can list the column names for you.

Will the 'CurrentProject.Connection.Execute "Delete * from MyTable Where
ID=" & strID' line work on a ADODB recordset?

Also, please look at my response to Randy Harris in this post, too. It
includes my code for generating the SQL search statement.

Thanks.

 

[Alex Dybenko]

Hi Brian,
now it is clear. once you use aggregate (group by, Sum, etc) - you query
become read-only.
in this case i suggest that you use CurrentProject.Connection.Execute to
delete records directly from table, and then refresh recordset if necessary

 

[Brian]

Hey. Thanks for your help. I finally got it working correctly. I did not
realize that aggregation made the query read-only.

Thanks again!!