Importing Word form field data into Access database

[Brian Beck]

I've been tasked with creating a stand-alone script that will take the data
from the form fields in a Word 2003 document and import that data into a
database in Access 2003. The Word document here is always the same and the
form fields do not change...however, there will likely be numerous copies of
this document, all populated with different data in the form fields. The
Access database has fields that correspond to the same fields in the Word
document, but their names are not the same. (i.e. UserFirstName in the Word
document and DTCFirstName in the Access database.) Additionally, the
datatypes for the form fields in the Word document do not all match up with
their corresponding fields in Access.

With that all in mind, here is my question:

What is the best way to go about scripting this? If I were doing it
manually, I would likely save just the form field data from the Word file
into a comma delimited text file and then turn around and import that text
file into the database...but is that the best route from a programming
standpoint?

Any other ideas on getting this started are greatly appreciated.

-Brian Beck

 

[John Nurick]

Hi Brian,

Basically it's mostly ordinary Word automation: there are lots of
examples on the web; see especially http://word.mvps.org and
www.mvps.org/access.

Having opened the document, you access the data through its FormFields
collection. If you read each field's contents into a VBA variable, you
can adjust data types and do any other tidying up you need.

Then append the document's data to your table(s), either using recordset
operations or by assembling and executing a SQL INSERT INTO ... VALUES
.... query.

Finally close the document and get Word to open the next one.