{"id":64723,"date":"2007-06-05T23:29:00","date_gmt":"2007-06-05T23:29:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/06\/05\/how-can-i-append-a-value-to-a-specified-field-for-each-record-in-a-database\/"},"modified":"2007-06-05T23:29:00","modified_gmt":"2007-06-05T23:29:00","slug":"how-can-i-append-a-value-to-a-specified-field-for-each-record-in-a-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-append-a-value-to-a-specified-field-for-each-record-in-a-database\/","title":{"rendered":"How Can I Append a Value to a Specified Field For Each Record in a Database?"},"content":{"rendered":"

\"Hey, \n

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

— A <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, A. It\u2019s day 2 of TechEd 2007<\/B><\/A> and the Scripting Guys are still sitting here in the CMP Media booth (that\u2019s booth No. 1301 for those of you attending TechEd); they\u2019re busy handing out copies of Dr. Scripto\u2019s Fun Book<\/I>, holding drawings for Dr. Scripto bobblehead dolls, and otherwise just hanging around and having fun. But we haven\u2019t forgotten those we\u2019ve left behind. Remember, you<\/I> can win a Dr. Scripto bobblehead, too: you just need to enter the TechEd Challenge<\/B><\/A>. 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<\/B><\/A> in Windows Vista and Windows Server 2008. If you can\u2019t make it to Disney World, well, using scripts to burn CDs in Windows Vista is definitely the next best thing.<\/P>\n

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

\u201cGo through that door and turn left. The bathrooms are just down the hall.\u201d<\/P>\n

\u201cNo, you still have time: lunch is served until 1:00 PM today.\u201d<\/P>\n

\u201cYes, that\u2019s true: the attendee party is Thursday night at Universal Islands of Adventure.\u201d<\/P>\n

See? Bathrooms, lunch, parties: where would Microsoft customers be without the Scripting Guys to help answer questions?<\/P>\n

Just a second, someone\u2019s asking Jean another question: \u201cHow can I add the letter s<\/I> to the end of each record in a database field?\u201d What an amazing coincidence; thank goodness we have today\u2019s column, huh? Here\u2019s<\/I> how you, or anyone else, can add the letter s<\/I> to the end of each record in an Access database field:<\/P>

Const adOpenStatic = 3\nConst adLockOptimistic = 3<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = c:\\scripts\\test.mdb” <\/p>\n
objRecordSet.Open “UPDATE TextFiles SET FileName = FileName & ‘s'”, _\n    objConnection, adOpenStatic, adLockOptimistic<\/p>\n
objRecordSet.Close\nobjConnection.Close\n<\/PRE>\n
Sorry; we have to answer another question here. No, the Technical Learning Center closes at 6:00 PM. No thanks necessary; that\u2019s what we\u2019re here for.<\/P>\n
OK, back to the script. We start out by defining a pair of constants: adLockOptimistic and adOpenStatic. We aren\u2019t going to discuss these constants in any detail today; if you\u2019d like to know more about these things and what they are used for then take a look at our Scripting Guys webcast<\/B><\/A> on working with databases. For now, it\u2019s enough to know that these constants will define the record-locking and cursor type for our recordset.<\/P>\n
After defining the constants we create instances of the ADODB.Connection<\/B> and ADODB.Recordset<\/B> objects; we then use the following line of code to open the database C:\\Scripts\\Test.mdb:<\/P>
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = C:\\Scripts\\Test.mdb”\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note:<\/B> If you\u2019re running this against an Access 2007 database your Open statement will look a little different. Not only is the file extension on the database file different (.accdb rather than .mdb) but the provider is a little different too: <\/P>
objConnection.Open _\n    “Provider = Microsoft.ACE.OLEDB.12.0; ” & _\n        “Data Source = C:\\Scripts\\Test.accdb”\n<\/PRE><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
As it turns out, Test.mdb is a database that\u2019s leftover from a previous Hey, Scripting Guy! <\/I>column. But that\u2019s 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<\/I> to the FileName field for every record in the table by using one line of code:<\/P>
objRecordSet.Open “UPDATE TextFiles SET FileName = FileName & ‘s'”, _\n        objConnection, adOpenStatic, adLockOptimistic\n<\/PRE>\n
Cool, huh? In case you\u2019re wondering, the heart and soul of this line of code is the SQL query itself:<\/P>
UPDATE TextFiles SET FileName = FileName & ‘s’\n<\/PRE>\n
What we\u2019re doing here is using an Update<\/B> query to update all the records in our recordset. (In this case, of course, that\u2019s going to be every record in the TextFiles table.) And how<\/I> are we going to update these records? We\u2019re going to do that by setting the value of the FileName field (SET FileName =<\/B>) to the existing value of the FileName field plus<\/I> the letter s<\/I> (FileName & ‘s\u2019<\/B>). 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<\/I>. And because the letter s<\/I> is a string value, we do need to enclose that<\/I> in single quote marks. <\/P>\n
It\u2019s kind of cumbersome-looking, but it all makes sense.<\/P>\n
And believe it or not, that\u2019s all we have to do: no sooner do we issue the query than the letter s<\/I> will be tacked onto the value of the FileName field for every single record in the table. Talk about easy, huh?<\/P>\n
After that there\u2019s nothing left to do except call the Close<\/B> 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.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note:<\/B> Here\u2019s another difference between Access 2007 and previous versions. In Access 2007, the script will run and the database will be changed, but you\u2019ll receive an error when you try to close the recordset. Just remove this line from the script and it will run fine:<\/P>
objRecordSet.Close\n<\/PRE><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
What\u2019s that? Sorry, Peter and Dean<\/B><\/A> aren\u2019t here. No, we\u2019re afraid that Peter and Dean won\u2019t be attending TechEd at all this year. You say it\u2019s too bad that the Scripting Guys sent the second team to TechEd rather than the big stars? Well, we apologize for that, but \u2013 hey, wait a minute; we resent that. OK, sure, we do<\/I> apologize for sending Jean Ross. But Greg Stemp a second-teamer<\/I>? That hurts \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s day 2 of TechEd 2007 and the Scripting Guys are still sitting here in the CMP Media booth (that\u2019s booth No. 1301 for those of you attending […]<\/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-64723","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! 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\u2019s day 2 of TechEd 2007 and the Scripting Guys are still sitting here in the CMP Media booth (that\u2019s booth No. 1301 for those of you attending […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64723","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=64723"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64723\/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=64723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}