Software

How do I... Transfer data from a Word form to an Access database?

<p class="MsoNormal"><img src="http://t.cbsimg.net/i/z/200606/how_110x85.jpg" align="right" border="0" height="85" hspace="5" vspace="5" width="110" />Although Access forms are powerful interface objects, your users might not be familiar with them. However, your users may be very familiar with Word forms. Susan Sales Harkins shows you how to use a Word form to collect data and then transfer that data to an Access table. The technique requires an Access database, a Word form, and a bit of Visual Basic for Applications (VBA) code.</p>

Although Access forms are powerful interface objects, your users might not be familiar with them. In some cases, Access might not be installed on every users system, or you might prefer users not have access to your database.

Here is how to use a Word form to collect data from users and then transfer that data to an Access table. The technique requires an Access database, a Word form, and a bit of Visual Basic for Applications (VBA) code. (Instructions for Word 2003 and 2007 are given, but the technique will work in versions 2000, XP, and 2002.)

This blog post is also available in PDF form as a TechRepublic download.

On the database side

For the sake of simplicity, the example Word form transfers data to Northwind, the sample database that comes with Access. When applying this technique to your own work, you will need to know the following details before you create the Word form:

  • The database's path and name
  • The Access table's name
  • The table's field names and data types
The example Word form, shown in Figure A, will update two fields in the Shippers table: CompanyName and Phone. Both fields are text fields. The example path is:
C:Program FilesMicrosoft Office11Office11SamplesNorthwind.mdb

You may need to update this path to accommodate your system.

Figure A

A Word form makes an easy-to-use data entry form for collecting Access data

Collecting the data in a Word form

A Word form is a document that contains fill-in blanks, called fields, in which you enter data. A field is a predefined cell that stores and accepts data input. The example Word form in Figure A contains two text fields. Using this form, users can update the Shippers table in Northwind, the sample database that comes with Access, without launching Access or even knowing a thing about the database.

The Shippers table has three fields but one of them is an AutoNumber data type. When the form transfers the new record, Access will generate that value for you. That's why there are only two fields in the form: txtCompanyName and txtPhone.

To create the example Word form, insert two text fields into a Word document as follows:

  1. From the View menu, choose Toolbars and then select Forms.
  2. Insert two Text Form Field controls, with a blank line between them.
  3. Double-click a field to display its Field Options dialog box.
  4. Using the Bookmark property, identify the first field as txtCompanyName, as shown in Figure B.
  5. Repeat step 4 and identify the second control as txtPhone.
  6. Save the form.

Figure B

Identify both text form controls

In Word 2007, you may need to add the Developer tab as follows:

  1. Click the Office button and then click Word Options (in the bottom right corner).
  2. Click Popular.
  3. Check the Show Developer tab in the Ribbon option, and click OK.

Notice that the Word field names are similar to the Access field names, CompanyName and Phone. The only difference is the txt prefix. It isn't necessary to name your Word fields this way, but doing so makes it easier to match Word fields to Access fields. (The txt tag identifies the field as a text field.)

Once you have a form to fill out, you need the VBA function that transfers input values to an Access database. To add the function, do the following:

  1. Open the Visual Basic Editor (VBE) by pressing Alt+F11.
  2. Choose Module from the Insert menu.
  3. Enter the function in Listing A. Be sure to update the path if yours differs from the example's path.
  4. From the Tools menu, choose References and check the Microsoft ActiveX Data Objects 2.x Library (ADO) as shown in Figure C. (It isn't enough to highlight the library item; you must check it.) The Word Object and VBA libraries should already be referenced.
  5. Click OK to return to the module.

Listing A

Sub TransferShipper()

'Transfer new shipping company record to

'Shippers table in Northwind database.

Dim cnn As ADODB.Connection

Dim strConnection As String

Dim strSQL As String

Dim strPath As String

Dim doc As Word.Document

Dim strCompanyName As String

Dim strPhone As String

Dim bytContinue As Byte

Dim lngSuccess As Long

Set doc = ThisDocument

On Error GoTo ErrHandler

strCompanyName = Chr(39) & doc.FormFields("txtCompanyName").Result & Chr(39)

strPhone = Chr(39) & doc.FormFields("txtPhone").Result & Chr(39)

'Confirm new record.

bytContinue = MsgBox("Do you want to insert this record?", vbYesNo, "Add Record")

Debug.Print bytContinue

'Process input values.

If bytContinue = vbYes Then

strSQL = "INSERT INTO Shippers " _

& "(CompanyName, Phone) " _

& "VALUES (" _

& strCompanyName & ", " _

& strPhone & ")"

Debug.Print strSQL

'Substitute path and connection string with DSN if available.

strPath = "C:Program FilesMicrosoft Office11Office11SamplesNorthwind.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source = " & strPath

Debug.Print strConnection

Set cnn = New ADODB.Connection

cnn.Open strConnection

cnn.Execute strSQL, lngSuccess

cnn.Close

MsgBox "You inserted " & lngSuccess & " record", _

vbOKOnly, "Error Added"

doc.FormFields("txtCompanyName").TextInput.Clear

doc.FormFields("txtPhone").TextInput.Clear

End If

Set doc = Nothing

Set cnn = Nothing

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, _

vbOKOnly, "Error"

On Error GoTo 0

On Error Resume Next

cnn.Close

Set doc = Nothing

Set cnn = Nothing

End Sub

Figure C

Reference the ADO library
Return to the Word form and double-click txtPhone. In the resulting Options dialog box, choose TransferShipper from the Exit option's dropdown list, as shown in Figure D. That way, exiting the last field in the form triggers the code that transfers the input values to Access.

For our purposes, this is the easiest way to execute the code. You may prefer another method, such as adding a custom control to a toolbar. After you specify TransferShipper as the Exit property, close the dialog.

Figure D

Execute the function from the last control's Exit option

Now you're ready to protect the form. To do so, click the Protect Form tool on the Forms toolbar. Save the form and close it.

Using the form

Open the Word form and enter a value into both fields, as shown in Figure E. The Phone field in the Shippers table will accept the phone value in most any format. When you apply this technique, be sure to accommodate special formatting properties.

Figure E

Enter a new shipping company record

After entering a phone number, press Tab to exit the field, which triggers the TransferShipper() sub function (also known as a macro). After a number of declaration statements, the code concatenates several Chr() functions to the input values in order to insert delimiter characters. In this case, the Chr(39) function returns a single quote character ('). Dates require a pound sign (#). Numeric values don't require a delimiter.

The simple message in Figure F allows you to confirm the transfer of the current input values (Figure E). This is also the spot to check the user's input values for valid data. For instance, you might want to check for an empty field or an inappropriate data type.

Figure F

Confirm the new record

When you click Yes, the code builds a SQL INSERT INTO statement that includes the Access fields and the input values in the form:

INSERT INTO accesstable (acessfld1, acessfld2, ...)
VALUES (wordinputfld1, wordinputfld2, ...)

You don't have to include an input value for every field in the Access table, although you must include an input value for every required field. Be sure the order of the Access and Word input fields match. If you omit the Access field references, you must include a Word input value for every field, except an AutoNumber field.

Next, the code identifies the path to Northwind and opens a connection. Be sure to include a password if required using the following syntax:

cnn.Open connectionstring, userid, password, options

If a Data Source Name (DSN) for the database exists, reference it as follows:

cnn.Open = "DSN=datasourcename"

A DSN is easier to work with than a complex connection string. Finally, the Execute method inserts the data into the Shippers table. The database can be open, but you'll want to account for multi-users and locking possibilities.

Figure G shows a confirmation message that the transfer was successful. If you skip this step, be sure to account for errors at this level. If the INSERT INTO statement fails, you'll probably want to control that error in some way.

For example, you might clear the fields and prompt for new values. A better solution would be to identify the problem so you can avoid it. Just remember that the input data must satisfy all of the Access field properties. The two most common problems are:

  • Mismatched data types — That means you can't pass text to a numeric field, and so.
  • Missing required values at the Access (table) level — If an Access field's Required property is set to Yes, you can't pass a null value. If you leave txtCompanyName blank, the code, as is, returns an error because Access requires a value for that field.

Figure G

The form lets you know that the code inserted the new record
After a successful transfer, the code clears the Word fields. Error handling is rudimentary. Test this technique thoroughly and accommodate all the potential errors. Just in case, you don't believe it worked as expected, you can open the Shippers table in Access. Figure H shows the newly added record. (Don't worry about the missing AutoNumber values; they don't matter.)

Figure H

The Word form added a new shipping company to the Northwind Shippers table

Transfer complete

Knowing your data is the key to transferring each new record without incident. The example code contains the essentials you need to get started. You'll want to enhance the technique to accommodate your data and other requirements.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox