Hey, Scripting Guy! How Can I Delete a Set of Records From an Access Database?

ScriptingGuy1

Hey, Scripting Guy! We have an Access database that we use to keep track of resources that are associated with all of our projects. Periodically I need to go into the database and delete all the records tied to a particular project. Is there any way that I can do that using a script?

— WK

Hey, WK. Having been gone for nearly three weeks, the Scripting Guy who writes this column has a lot of catching up to do. For example, he hasn’t had a chance – until now – to mention that the Surgeon General of the United States (Steven K. Galson) believes that Santa Claus needs to go on a diet. “It is really important that the people who kids look up to as role models are in good shape, eating well and getting exercise,” said Galson in reference to Santa. “It is absolutely critical.”

Now, unlike most people in the US, the Scripting Guy who writes this column wasn’t upset by Galson’s remarks. For one thing, he doesn’t know too many children who look upon Santa Claus as a role model; that is, someone they might want to be like when they grow up. It’s safe to say that while most people like Santa Claus, very few people want to be like Santa Claus. (Although that the red suit is pretty snazzy.)

To tell you the truth, the Scripting Guy who writes this column was actually pretty excited to read about Dr. Galson criticizing Santa and his lifestyle. After all, if the Surgeon General of the United States is busy attacking a make-believe character that can only mean one thing: all the other health problems in the world have been solved! Poor Dr. Galson probably has nothing better to do with his time.

In case you’re wondering, the Scripting Guy who writes this column is also wholeheartedly behind the proposal that children leave Santa healthy snacks – like fruit and vegetables – rather than the more traditional Christmas cookies. After all, a healthy lifestyle is important for anyone, even for – maybe even especially for – make-believe characters.

Besides, the fewer cookies left out for Santa the more cookies available for … others ….

To be honest, this whole thing seems like a tempest in a teapot. After all, everyone knows who children really aspire to be: the Microsoft Scripting Guys. That’s not because we are in good shape (unless you think that round is a good shape), eat well, or get exercise. Instead, it’s because the Scripting Guys – unlike either Santa Claus or Dr. Steven K. Galson – are able to write scripts that can delete a set of records from a Microsoft Access database:

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "DELETE * FROM Projects WHERE " & _
    "ProjectName = 'Workstation Upgrade'", objConnection, adOpenStatic, adLockOptimistic

objConnection.Close

As you can see, there isn’t much work (or code) required to delete a set of records from a database. To begin with, we define a pair of constants (adOpenStatic and adLockOptimistic), setting the value of each of these constants to 3. We aren’t going to talk about these constants in any detail today; suffice to say that: 1) they’re used to configure the parameters for opening the database and the database table; and 2) you can learn more about them by taking a peek at the Scripting Guys webcast Database Scripting For System Administrators.

Which, incidentally, was recently voted the third-best Christmas film of all time, finishing just behind It’s a Wonderful Life and A Christmas Story. We’re quite proud of that, considering the fact that this webcast not only doesn’t have anything to do with Christmas, but isn’t even a film.

After we define our constants we then create instances of two objects. ADODB.Connection, as the name implies, is the object that handles our connection to the Access database; ADODB.Recordset is the object that, well, handles the recordset we’ll be retrieving from the database. (Any time you retrieve data from a database the returned data is known as a recordset.) As soon as we create these two objects we can then use the Open method to open the database file C:\Scripts\Test.mdb:

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb"

Well, OK, one clarification: the preceding code works on Access databases prior to Office 2007. If you’re running this script against an Access 2007 database your Open statement needs to look a little different; in particular, you’ll need to use a different file extension (.accdb rather than .mdb) and modify the Provider value as well. In other words, you need to use this line of code:

objConnection.Open _
    "Provider = Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source = C:\Scripts\Test.accdb"

What we’re assuming here is that Test.mdb contains all our project-related information; in particular, we’re assuming that Test.mdb contains a table named Projects. In turn, we’re assuming that the Projects table includes a field named ProjectName. Now, suppose we want to delete all the records related to a project named Workstation Upgrade; in other words, suppose we want to delete all the records that have a ProjectName equal to Workstation Upgrade. How are we going to do that? Well, now that we have a connection to the database we can do that by executing this one little line of code:

objRecordSet.Open "DELETE * FROM Projects WHERE " & _
    "ProjectName = 'Workstation Upgrade'", objConnection, adOpenStatic, adLockOptimistic

The key part here is the SQL query. Most of us are used to writing SQL queries that retrieve data from a database table; that is, we’re used to writing SQL queries that look like this:

SELECT * FROM Projects WHERE ProjectName = 'Workstation Upgrade'

That query, as you probably know, returns all the records from the Projects table that have a ProjectName equal to Workstation Upgrade. Now take a look at our SQL query, a query that deletes all the records from the Projects table that have a ProjectName equal to Workstation Upgrade:

DELETE * FROM Projects WHERE ProjectName = 'Workstation Upgrade'

Look familiar? As it turns out, the only difference between retrieving a set of records and deleting a set of records is this: to retrieve records you use the SELECT keyword, and to delete records you use the DELETE keyword. As long as you can remember that you’re in business.

Incidentally, don’t let the name of the method we use – Open – throw you. In a sense we are opening up a recordset: in fact, we’re opening it up just long enough to delete a bunch of records from it. When we do so, the recordset will automatically open and then close again; as you might have noticed, at the end of the script we use the Close method to close the database connection, but we don’t have any code to close the recordset. That’s because we don’t have to include any to close the recordset. In fact, if you added the line objRecordset.Close you’d end up generating a “Can’t close something that isn’t even open” error and your script would then crash at that point.

One less line of code to write. Consider that a late Christmas present from the Scripting Guys.

That should do it, WK. By the way, if you’re upset over the latest attack on Santa Claus, well, we understand; after all, traditionally Santa has always been portrayed as being on the portly side. For example, who can forget the immortal words of Clement Clark Moore:

“He had a broad face and a little round belly,
That shook when he laughed, like a bowlful of jelly.
He was chubby and plump, a right jolly old elf,
And I laughed when I saw him, in spite of myself.”

Oops, sorry; that’s test results from the last physical examination that the Scripting Guy who writes this column took. But you get the idea.

ScriptingGuy1

Follow