Hey, Scripting Guy! How can I delete a record – or a set of records – from a disconnected recordset?

— SM

eHy sm.,.

Sorry; let’s try that again:

Hey, SM. We apologize for the poor typing the first time around; the Scripting Guy who writes this column is trying to adjust to typing with just one hand and just one eye. Last night this Scripting Guy and his Scripting Son engaged in a … spirited … game of basketball, a game which ended with up with:

And that was just in the first 5 minutes. After that the game began to get a little rough.

But do you think a few bumps and bruises would be enough to keep the Scripting Guy who writes this column from, well, writing this column? Well, to tell you the truth it would have. However, after seeing what passes for daytime television these days he decided he might as well come in to work and figure out how to delete a record from a disconnected recordset.

Before we begin we should note that we aren’t going to talk about disconnected recordsets in much detail today; for more information take a peek at the Microsoft Windows 2000 Scripting Guide or at this Scripting Week 2 webcast. For now we’ll just mention the fact that a disconnected recordset is like a database table that exists only in memory; it’s not tied to a real, live database file.

Second, as far as we know, you’re right, SM: you can’t query a disconnected recordset, at least not by using a query like Select * From or Delete * From. As you noted, you tried different variations on these queries and none of them seemed to work. For better or worse, that’s to be expected.

So is that going to be a problem? Let’s put it this way: after you’ve been flung headfirst into a wall, deleting a record from a disconnected recordset hardly seems like a problem at all.

Let’s start out by taking a look at a script that creates a simple disconnected recordset and then deletes a record from that recordset. We’ll explain how this sample script works, then show you a modified version that will prove that it really does delete a record from the disconnected recordset.

Here’s the script:

Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open
arrItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)
For Each strItem in arrItems
    DataList.AddNew
    DataList(“Name”) = strItem
    DataList.Update
Next
DataList.Filter = “Name = ‘B'”
DataList.MoveFirst
Do While Not DataList.EOF
    DataList.Delete
    DataList.MoveNext
Loop
DataList.Filter = “”

Like we said, we don’t have the time to discuss the ins and outs of disconnected recordsets in today’s column. (Besides, you try typing a detailed discussion of disconnected recordsets with just one hand!) Therefore we’ll simply note that the following block of code creates – and then opens – a disconnected recordset consisting of a single field (Name):

Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open

Once we have a disconnected recordset our next step is to add some data to the thing. We said this was going to be simple, and we meant it: our recordset is going to consist of the letters A, B, C, D, E, F,and G. In preparation for adding these records to the recordset we first store each of the letters in an array named arrItems:

arrItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)

We then set up a For Each loop to loop through each of the items in the array. For each of these items we call the AddNew method to create a new record in the disconnected recordset. We set the value of the Name field to the value of the array item, then call the Update method to write the new record to the recordset. That’s what we do in this block of code:

For Each strItem in arrItems
    DataList.AddNew
    DataList(“Name”) = strItem
    DataList.Update
Next

In turn, that gives us a disconnected recordset consisting of the following records:

A
B
C
D
E
F
G

Having worked so hard to add these records to the recordset we’re now going to turn right around and delete one of these records.

Good question: how are we going to do that when we can’t even query a disconnected recordset? Well, what we can do is add a Filter to the recordset. This will have the effect of temporarily filtering out all the records in the recordset except those we want to delete. We can then go ahead and delete those records and then remove the filter.

Maybe this will make more sense as we walk you through it. First, take a look at the filter we apply to the recordset:

DataList.Filter = “Name = ‘B'”

There’s really nothing magical about this filter; it simply says that, for the moment anyway, we want to work with only those records where the value of the Name field is equal to B. Because we have only one record in the recordset that has the Name B only one record will be deleted; if we had multiple records with the Name B all of those records would be deleted.

After applying the filter we use the MoveFirst method to move to the first record in our recordset, a recordset which – thanks to the Filter – now consists of just a single record. We then use this block of code to loop through the recordset and delete each and every record in the filtered recordset (which, again, will just be the one record that has the Name B):

Do While Not DataList.EOF
    DataList.Delete
    DataList.MoveNext
Loop

When we’re done we remove the filter by setting the Filter property to an empty string:

DataList.Filter = “”

And there you have it: we’ve deleted a record from a disconnected recordset, despite having jammed our deleting finger.

Of course, in the preceding script there’s no way to verify that the record really did get deleted. Because of that we put together a modified version of the script. In this script we echo back all the records in the recordset before we apply the filter. We apply the filter, delete the record, then remove the filter and echo back the recordset again. If all goes well, record B should have disappeared.

Here’s the code:

Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open
colItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)
For Each strItem in colItems
    DataList.AddNew
    DataList(“Name”) = strItem
    DataList.Update
Next
DataList.MoveFirst
Do While Not DataList.EOF
    Wscript.Echo DataList.Fields.Item(“Name”)
    DataList.MoveNext
Loop
Wscript.Echo
DataList.Filter = “Name = ‘B'”
DataList.MoveFirst
Do While Not DataList.EOF
    DataList.Delete
    DataList.MoveNext
Loop
DataList.Filter = “”
Do While Not DataList.EOF
    Wscript.Echo DataList.Fields.Item(“Name”)
    DataList.MoveNext
Loop

And here’s the output:

A
B
C
D
E
F
G

A
C
D
E
F
G

Hopefully record B is no longer in the recordset. (We can’t verify that ourselves because our left eye is starting to water up again. But we’re pretty sure it worked.)