{"id":63363,"date":"2008-01-03T21:18:00","date_gmt":"2008-01-03T21:18:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/01\/03\/hey-scripting-guy-how-can-i-delete-a-set-of-records-from-an-access-database\/"},"modified":"2008-01-03T21:18:00","modified_gmt":"2008-01-03T21:18:00","slug":"hey-scripting-guy-how-can-i-delete-a-set-of-records-from-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-delete-a-set-of-records-from-an-access-database\/","title":{"rendered":"Hey, Scripting Guy! How Can I Delete a Set of Records From an Access Database?"},"content":{"rendered":"

\"Hey, <\/p>\n

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?<\/p>\n

— WK<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

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\u2019t had a chance \u2013 until now \u2013 to mention that the Surgeon General of the United States (Steven K. Galson) believes that Santa Claus needs to go on a diet. \u201cIt is really important that the people who kids look up to as role models are in good shape, eating well and getting exercise,\u201d said Galson in reference to Santa. \u201cIt is absolutely critical.\u201d<\/p>\n

Now, unlike most people in the US, the Scripting Guy who writes this column wasn\u2019t upset by Galson\u2019s remarks. For one thing, he doesn\u2019t 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\u2019s safe to say that while most people like Santa Claus, very few people want to be<\/i> like Santa Claus. (Although that the red suit is<\/i> pretty snazzy.)<\/p>\n

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. <\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Interestingly enough, Dr. Galson isn\u2019t even the real<\/i> Surgeon General; he\u2019s only the acting Surgeon General. That means this might actually be a battle between two<\/i> make-believe characters, Santa Claus and the Acting Surgeon General of the United States.<\/p>\n

Yes, exactly<\/i> like Alien vs. Predator.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

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

Besides, the fewer cookies left out for Santa the more cookies available for \u2026 others \u2026.<\/p>\n

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

Const adOpenStatic = 3\nConst adLockOptimistic = 3\n\nSet objConnection = CreateObject(\"ADODB.Connection\")\nSet objRecordSet = CreateObject(\"ADODB.Recordset\")\n\nobjConnection.Open _\n    \"Provider = Microsoft.Jet.OLEDB.4.0; \" & _\n        \"Data Source = C:\\Scripts\\Test.mdb\" \n\nobjRecordSet.Open \"DELETE * FROM Projects WHERE \" & _\n    \"ProjectName = 'Workstation Upgrade'\", objConnection, adOpenStatic, adLockOptimistic\n\nobjConnection.Close\n<\/pre>\n
As you can see, there isn\u2019t 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\u2019t going to talk about these constants in any detail today; suffice to say that: 1) they\u2019re 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<\/b><\/a>. <\/p>\n
Which, incidentally, was recently voted the third-best Christmas film of all time, finishing just behind It\u2019s a Wonderful Life<\/i> and A Christmas Story<\/i>. We\u2019re quite proud of that, considering the fact that this webcast not only doesn\u2019t have anything to do with Christmas, but isn\u2019t even a film.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. OK, we might<\/i> have made that last part up. We\u2019ll have to double-check our sources and get back to you on that.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
After we define our constants we then create instances of two objects. ADODB.Connection<\/b>, as the name implies, is the object that handles our connection to the Access database; ADODB.Recordset <\/b>is the object that, well, handles the recordset we\u2019ll 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<\/b> method to open the database file C:\\Scripts\\Test.mdb:<\/p>\n
objConnection.Open _\n    \"Provider = Microsoft.Jet.OLEDB.4.0; \" & _\n        \"Data Source = C:\\Scripts\\Test.mdb\"\n<\/pre>\n
Well, OK, one clarification: the preceding code works on Access databases prior to Office 2007. If you\u2019re running this script against an Access 2007 database your Open statement needs to look a little different; in particular, you\u2019ll need to use a different file extension (.accdb rather than .mdb) and modify the Provider<\/b> value as well. In other words, you need to use this line of code: <\/p>\n
objConnection.Open _\n    \"Provider = Microsoft.ACE.OLEDB.12.0; \" & _\n        \"Data Source = C:\\Scripts\\Test.accdb\"\n<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Why didn\u2019t we show you the Office 2007 version of the script first<\/i>? After all, the Scripting Guy who writes this column uses Office 2007, doesn\u2019t he?<\/p>\n
You know what? We\u2019ll have to double-check our sources and get back to you on that.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
What we\u2019re assuming here is that Test.mdb contains all our project-related information; in particular, we\u2019re assuming that Test.mdb contains a table named Projects. In turn, we\u2019re 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<\/i>; in other words, suppose we want to delete all the records that have a ProjectName equal to Workstation Upgrade<\/i>. 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:<\/p>\n
objRecordSet.Open \"DELETE * FROM Projects WHERE \" & _\n    \"ProjectName = 'Workstation Upgrade'\", objConnection, adOpenStatic, adLockOptimistic\n<\/pre>\n
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\u2019re used to writing SQL queries that look like this:<\/p>\n
SELECT * FROM Projects WHERE ProjectName = 'Workstation Upgrade'\n<\/pre>\n
That query, as you probably know, returns all the records from the Projects table that have a ProjectName equal to Workstation Upgrade<\/i>. Now take a look at our<\/i> SQL query, a query that deletes<\/i> all the records from the Projects table that have a ProjectName equal to Workstation Upgrade<\/i>:<\/p>\n
DELETE * FROM Projects WHERE ProjectName = 'Workstation Upgrade'\n<\/pre>\n
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\u2019re in business.<\/p>\n
Incidentally, don\u2019t let the name of the method we use \u2013 Open<\/b> \u2013 throw you. In a sense we are<\/i> opening up a recordset: in fact, we\u2019re 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<\/b> method to close the database connection, but we don\u2019t have any code to close the recordset. That\u2019s because we don\u2019t have<\/i> to include any to close the recordset. In fact, if you added the line objRecordset.Close<\/b> you\u2019d end up generating a \u201cCan\u2019t close something that isn\u2019t even open\u201d error and your script would then crash at that point.<\/p>\n
One less line of code to write. Consider that a late Christmas present from the Scripting Guys.<\/p>\n
That should do it, WK. By the way, if you\u2019re 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:<\/p>\n
\u201cHe 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.\u201d <\/p>\n
Oops, sorry; that\u2019s test results from the last physical examination that the Scripting Guy who writes this column took. But you get the idea.<\/p>\n","protected":false},"excerpt":{"rendered":"
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? — […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,54,49,3,5],"class_list":["post-63363","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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? — […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63363"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63363\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}