How Can I Delete a Record From a Disconnected Recordset?

Hey, Scripting Guy! Question

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

— SM

Spacer Hey, Scripting Guy! Answer Script Center

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:

The Scripting Dad jamming his finger while blocking a shot. (To add insult to injury he did block the shot, but the Scripting Son simply picked the ball up and made the basket anyway.)

The Scripting Son poking the Scripting Dad in the eye while trying to steal the ball.

The Scripting Son “accidentally” shoving the Scripting Dad headfirst into a wall.

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.

Note. In all fairness, the Scripting Son did not fling his father headfirst into the wall and then simply walk away without a word. Instead he turned to his dad and said, in all sincerity, “You traveled. My ball.”

Of course, had the circumstances been reversed, the Scripting Dad probably would have said the same thing.

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:

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):

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:

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:

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

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:

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.

Note. Yes, if we had a “fancier” recordset we could apply a fancier filter, one that used an AND clause or an OR clause or, well, pretty much anything short of a Santa clause.

Hey, come on, give us a break: after all, we did get smashed headfirst into a wall last night.

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):

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

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:

And here’s the output:

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.)


No Comment.