Convert data from Word to Access database

Although Access is a database application that has strong interface objects, users are not familiar with this application. In many cases, Access is not installed on any user system, or you may not want users to access your database.

In this article TipsMake.com will guide you how to use Word application to collect user data and then transfer all of this data to a table on Access. This method requires an Access, Word database application and Visual Basic for Applications (VBA) codes. (The instructions below are implemented in Word 2003 and 2007 but this method will also be compatible with Win 2000, XP and 2002 versions).

Note on the database

For simplicity, imagine that Word will transfer data to somewhere and a similar database will be transferred to Access. When applying this method at work, you need to know the following details before creating the word file:

1. Path and name of the database.
2. Table name on Access.
3. Name the fields on the table and the data type.

The example is executed on the form (form) of Word (Figure A), you update two fields in the Shippers table: CompanyName and Phone . Both of these fields are in text form. The example path is:

C: Program FilesMicrosoft Office11Office11SamplesNorthwind.mdb

You may need to update the path to adjust the system.

Convert data from Word to Access database Picture 1
Figure A: Word application makes it easy to enter data to gather data for Access.

Collection of data from the Word form

Word form is a document that contains empty cells called fields so that you can enter data. The field is a predefined cell that stores and accepts input data. In the Word form example as shown in Figure A, we have two input fields. Using this application, users can update the Shippers table and then similar databases will be transferred to Access without having to run Access or even you don't need to understand the database.

The Shippers table has 3 fields, but one of them is automatic numbering ( AutoNumber ). When the application passes a new record, Access will sort the values. That's why only two fields are displayed: txtCompanyName and txtPhone .

To create an example on a Word form, insert two text fields (characters) into the Word document as follows:

1. From the View menu, select Toolbars and then select Forms .

2. Insert two Text Form Field controls and how to line between them.

3. Double-click a field to display the Field Options dialog box.

4. Using theBookmark property to define the first field is txtCompanyName , Figure B.

5. Repeat step 4 and define the second control as txtPhone .

6. Save the file.

Convert data from Word to Access database Picture 2
Figure B: Define two text controls

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

1. Click the Office button and then click Word Options (in the lower right corner).

2. Click Popular .

3. Select the Show Developer tab in the Ribbon option and click OK .

Note that the field names on Word must be the same as the field names on Access, which will be CompanyName and Phone . The only difference is the txt prefix. There is no need to name the fields in Word that way, but this naming will make it easy to match between fields in Word and on Access. ( txt to determine this is a text entry field).

Once the document is completed, you need the VBA function to convert the imported values ​​into an Access database. Follow these steps to add a function:

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 .

2. Select Module from the Insert menu.

3. Enter the function in Code A. Be sure to update the path correctly if your path is different from the example.

4. From the Tools menu, select References and check Microsoft ActiveX Data Objects 2.x Library (ADO) as shown in Figure C. (It will not select this library item yourself, you must select it). Word Object and VBA library will be referenced.

5. Click OK to return to the module.

A code

Sub TransferShipper ()

'Transfer new record company

'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 values ​​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

Sub Exit

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

Convert data from Word to Access database Picture 3
Figure C: ADO library reference

Go back to the Word application and double click at txtPhone . In the Options dialog box, select TransferShipper from the Exit drop-down list, Figure D. Do so until the last field has been assigned the code to transfer input data to Access.

This is the easiest way to execute code. You can choose to use other methods such as adding random controls on the toolbar. After you specify TransferShipper in the Exit property. Finally, close the dialog box.

Convert data from Word to Access database Picture 4
Figure D: Execute the function from the Exit control of the last control

You will now protect the form created on Word. Click Protect Form on the Forms toolbar. Save the entire sample sheet and close it.

Use the form

Open the Word form file and enter a value in both fields, Figure E. Phone field on the Shippers table will accept phone value for almost all formats. When applying this method, make sure you provide special formatting attributes.

Convert data from Word to Access database Picture 5
Figure E: Enter a new record

After entering the phone number, press Tab to end that field, the TransferShipper () child function will work (known as a macro). After the number declaration statements, the code will concatenate some Chr () functions to enter the value to add special characters. In this case, the Chr (39) function returns the value ( ' ). Date requires characters ( # ). Numeric values ​​do not require special characters.

The simple message you see in Figure F allows you to reconfirm the data transfer process (Figure E). This is also a sign to check the correct input values ​​of the user. For example, you might want to check an empty field or an inappropriate data type.

Convert data from Word to Access database Picture 6
Figure F: Confirm the new record

When you click Yes , the code will build an SQL INSERT INTO statement that includes Access fields and values ​​entered into the sample table:

INSERT INTO accesstable (acessfld1, acessfld2, .)

VALUES (wordinputfld1, wordinputfld2, .)

You do not need to enter values ​​for every field in the Access table, but you need to enter the required fields. Pay attention to the matching order of data entry fields in Access and Word. If you omit a reference to the Access field, you must also omit an input value in Word, the AutoNumber field does not have to be entered.

Next, the code will identify the path and open the connection. Note that you can include a password if you have set a password for an Access file, using the following syntax:

cnn. Open connectionstring, userid, password, options

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

cnn.Open = "DSN = datasourcename"

A DSN is easier to work with than a complex chain of connections. Finally, the Execute method will put the data into the Shippers table. The database can be opened but you will want to count many users and block capabilities.

Figure G shows a message confirming the data transfer process is completed. If you skip this step, an error will occur. If the INSERT INTO statement is broken, you will need to fix this problem in several ways.

For example, you can delete fields and add new values. A better solution is to identify errors to avoid them in time. Remember that the input data must match all of the field properties in Access. The two most common incidents are:

1. Incompatible data types - This means you cannot enter text in the numeric field and vice versa.
2. Ignore the required values ​​on the Access table - If the Access field's requested property is set to Yes , then you cannot enter the null value. If you leave txtCompanyName blank, the code will error because Access needs to have a value in that field.

Convert data from Word to Access database Picture 7
Figure G: Sample table showing the code that inserted the new record

After the conversion is successful, the code will clean up the fields in Word. Very basic error management. Check this technique thoroughly and consider all possible errors. In case, don't trust it to work as expected, you can open the Shippers panel on Access. Figure H shows the newly added record. (Don't worry about AutoNumber values; they're not important.)

Convert data from Word to Access database Picture 8
Figure H: Values ​​have been added to the table

Successful conversion

Knowing the data is the key to converting each new record without a serious problem. The example code contains the necessary skills to get you started. You need to improve the technology to adjust data and some other requirements.