Using Word for Data Entry into Access?

[AG777]

A client has a Word questionnaire of 200+ pages and thousands of questions. They want the hardcopy data keyed into Access.

Rather than duplicating the doc as Access forms, is it possible to use Automation to turn the Word doc into an input interface for an Access table? If so, can this be a "live" link, or can Word accumulate data which I would then import it into Access?

I've only seen Word used as an OUTPUT from Access -- e.g., mail merge. Seems like the reverse should be possible.

Any help would be appreciated!

 

[DJN]

Yes it is possible, but with that amount of data, it could prove problematic. A more complex table, which has multiple sections, merged cells, totals and subtotals, and so on, importing it won't be straightforward. Access tables have to be absolutely consistent: every record must have the same number of columns (fields), each column must have compatible data in every row (e.g. if you want a column in Word to become a numeric field in Access, every cell in the column must contain either a number or nothing, not even "-" or "n/a".)

The general idea is to rearrange the table until you get it into that state. Any rows of totals and subtotals you simply omit, because they can easily be recalculated in Access. Any merged cells have to be unmerged, and appropriate values put in each of them. Also, remove all the headings and notes and stuff, leaving a single row of column headings. These will translate to Access fieldnames, and therefore should not consist of reserved words such as
"Date" or "Time" and should preferably consist only of letters, digits and underscores. Often it helps to paste the table into an Excel worksheet to do all this. At the end, get it into either an Excel sheet or a comma- or
tab-separated textfile, and from there import it to Access.

 

[AG777]

Thanks for the reply, DJN, but it's not quite on target:

1. I plan to build the Access table from specs, not via import from Word.

2. Word is to be used for inputing the questionnaire answers; each entry is to be captured and either sent to Access directly or saved for batch import into Access.

I'm familiar with VBA and recordset methods, but not with the Word app.

 

[Pampers]

Hi AG777,
I have never worked with it, but wasn't MS InfoPath designed to these kind of things???

Pampers
Keeping it simple can be complicated

 

[JoeAtWork]

Hi AG777,

Speaking as one programmer to another, I don't see why not. Like you, I'm more used to doing it the other way around. But I think what you would do is use Word "Field" objects (note, not the same thing as database fields) for the questionnaire answers. When the user submits the questionnaire, I believe you would just iterate through the Fields to validate the data, and if it checks out than just use ADO to send the record to the database.

Really not much different than programming an unbound Access form.