ADO Find and bookmarking around deleted records

[Max Moor]

Hi All,
So here is a little code. In plain english, I'm using ADO's Find
method to iterate through all the Categories belonging to a specific
Section. If the Category is not being used in a RegistryTable, I want
to delete it.

The problem I have is that if I delete the Category, The Find at
the end of the loop complains that the bookmark refers to a record that
is deleted, or marked for deletion. How can I manipulate the bookmark
to work around this? The code is below... Any other comments are
welcome.

- Max



Set rstCats = New ADODB.Recordset
rstCats.Open "tblFinanceCategories", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect

If (Not rstCats.BOF) And (Not rstCats.EOF) Then

rstCats.Find "SecID = " & lngSecID

' Find all categories in this section
Do While (rstCats.EOF <> True)

bCatFound = False

' Get the CatID of the found cat record
lngCatID = rstCats!CatID

' Search for the category in the finance registry
vDLRet = DLookup("[CatID]", "tblRegistry", "[CatID]=" & lngCatID)

If (Not IsNull(vDLRet)) Then

' If found, can't delete the cat, or (later) the section
bDeleteSec = False

Else

' Cat not found in registry, delete it
rstCats.Delete
rstCats.Update

End If

vMark = rstCats.Bookmark
rstCats.Find "SecID = " & lngSecID, 1, adSearchForward, vBookmark

Loop

End If ' (Not rstTable.BOF) And (Not rstTable.EOF)

rstCategories.Close

 

[Michel Walsh]

Hi,


CurrentDb.Execute"DELETE DISTINCTROW Category.* FROM Category LEFT JOIN
Registry ON Category.cat=Registry.cat WHERE Registry.Cat IS NULL"



seems to be a good candidate to replace all your VBA code recordset-based
approach. (Use a backup to make your tests, not real data, since queries can
do "massive" modifications).


Hoping it may help,
Vanderghast, Access MVP

 

[Max Moor]

Hi,


CurrentDb.Execute"DELETE DISTINCTROW Category.* FROM Category LEFT
JOIN
Registry ON Category.cat=Registry.cat WHERE Registry.Cat IS NULL"

Hi Michael,

I hadn't done it that way, because I wanted to have a test for the
ones that were used in there. Of course, I can alwys do a DLookup or
something for that. I just got down my other road, and never thought about
doing it this way. Thanks for resetting me.

- Max