Hey, Scripting Guy! How can I add the letter s to the end of a specified field for each record in an Access database?

— A

Hey, A. It’s day 2 of TechEd 2007 and the Scripting Guys are still sitting here in the CMP Media booth (that’s booth No. 1301 for those of you attending TechEd); they’re busy handing out copies of Dr. Scripto’s Fun Book, holding drawings for Dr. Scripto bobblehead dolls, and otherwise just hanging around and having fun. But we haven’t forgotten those we’ve left behind. Remember, you can win a Dr. Scripto bobblehead, too: you just need to enter the TechEd Challenge. Plus we left some interesting reading material to help pass the time until we get back; today, for example, we have Part 1 of a two-part series on using scripts to work with CDs in Windows Vista and Windows Server 2008. If you can’t make it to Disney World, well, using scripts to burn CDs in Windows Vista is definitely the next best thing.

As for TechEd 2007, well, so far, so good. It’s kind of fun to man a booth here, at least in part because Microsoft doesn’t always give system administration scripting its due; it’s safe to say that many people who work at Microsoft don’t feel as though the Scripting Guys serve much of a purpose. But that’s not true. For example, right now Scripting Guy Jean Ross is manning our booth. Let’s listen in as she tries to help Microsoft customers:

“Go through that door and turn left. The bathrooms are just down the hall.”

“No, you still have time: lunch is served until 1:00 PM today.”

“Yes, that’s true: the attendee party is Thursday night at Universal Islands of Adventure.”

See? Bathrooms, lunch, parties: where would Microsoft customers be without the Scripting Guys to help answer questions?

Just a second, someone’s asking Jean another question: “How can I add the letter s to the end of each record in a database field?” What an amazing coincidence; thank goodness we have today’s column, huh? Here’s how you, or anyone else, can add the letter s to the end of each record in an Access database field:

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 “UPDATE TextFiles SET FileName = FileName & ‘s'”, _
    objConnection, adOpenStatic, adLockOptimistic
objRecordSet.Close
objConnection.Close

Sorry; we have to answer another question here. No, the Technical Learning Center closes at 6:00 PM. No thanks necessary; that’s what we’re here for.

OK, back to the script. We start out by defining a pair of constants: adLockOptimistic and adOpenStatic. We aren’t going to discuss these constants in any detail today; if you’d like to know more about these things and what they are used for then take a look at our Scripting Guys webcast on working with databases. For now, it’s enough to know that these constants will define the record-locking and cursor type for our recordset.

After defining the constants we create instances of the ADODB.Connection and ADODB.Recordset objects; we then use the following line of code to open the database C:\Scripts\Test.mdb:

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

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

As it turns out, Test.mdb is a database that’s leftover from a previous Hey, Scripting Guy! column. But that’s OK: as long as it has a table in it and as long as that table has a field in it, we can use Test.mdb for demonstration purposes. Assuming we have a table named TextFiles (which we do) and assuming that this table has a field named FileName (which it does) we can add an s to the FileName field for every record in the table by using one line of code:

objRecordSet.Open “UPDATE TextFiles SET FileName = FileName & ‘s'”, _
        objConnection, adOpenStatic, adLockOptimistic

Cool, huh? In case you’re wondering, the heart and soul of this line of code is the SQL query itself:

UPDATE TextFiles SET FileName = FileName & ‘s’

What we’re doing here is using an Update query to update all the records in our recordset. (In this case, of course, that’s going to be every record in the TextFiles table.) And how are we going to update these records? We’re going to do that by setting the value of the FileName field (SET FileName =) to the existing value of the FileName field plus the letter s (FileName & ‘s’). Notice the syntax there. Because FileName is the name of a field in the table we can include it in the query without having to surround that name with quote marks of any kind. The field name is followed by the ampersand (the standard character for concatenating information); in turn, the ampersand is followed by the value to be appended to the field: the letter s. And because the letter s is a string value, we do need to enclose that in single quote marks.

It’s kind of cumbersome-looking, but it all makes sense.

And believe it or not, that’s all we have to do: no sooner do we issue the query than the letter s will be tacked onto the value of the FileName field for every single record in the table. Talk about easy, huh?

After that there’s nothing left to do except call the Close method and discard the Recordset and Connection objects. At that point, you can go back to work, and we can go back to answering questions. In fact here comes someone now.

objRecordSet.Close

What’s that? Sorry, Peter and Dean aren’t here. No, we’re afraid that Peter and Dean won’t be attending TechEd at all this year. You say it’s too bad that the Scripting Guys sent the second team to TechEd rather than the big stars? Well, we apologize for that, but – hey, wait a minute; we resent that. OK, sure, we do apologize for sending Jean Ross. But Greg Stemp a second-teamer? That hurts ….