Capturing Data from Word To Access

I will be requested here in the near future to create a database and some sort of user interface that will automatically capture data placed in a Word document and import it into an Access database. A few questions: Should I simply create an Addin in Word or MS Access and place it on the 1 persons machine that will be using it? Since I rarely use MS Word for production, what is the best way to capture the data and shoot it into Access? References and code would be greatly appreciated.

Ok, here are the details. In the word doc., all data sources are bookmarks in Word. Like I said, references and samples would be greatly appreciated as I have never done this before. Carey

Increased points to 150..
LVL 1
CareyMBilyeuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cquinnCommented:
I would use object automation within the Access database.  Open up the document, retrieve the data and save it all from within Access.  The easiest way to retrieve the data is to use bookmarks within the document (or form field which have implicit bookmarks) and then retrieve the contents from within VBA code.  I have written just such a system and it works well.  Various offices around the country fill in Word forms and send them to central office by e-mail, or snail-mail on floppy.  The database then opens the docs, retrieves the data and stores it in the database.

The word forms have in built validation etc and ensure that it is in a reasonable format, and can be retrieved from specific bookmarks - no need to worry that the user may have deleted the bookmarks!
0
BrianWrenCommented:
Can you describe the proces you want to see a little better?

Is Word going to present a form of sorts?  Or is this more free-form?

What is the nature of the data to be captured: sentences? phrases? numbers?

What application do you want to have control to pull off this transfer?

Brian
0
CareyMBilyeuAuthor Commented:
Brian:

Is Word going to present a form of sorts?  Or is this more free-form?
Aty this time I am assuming that the Word portion will be free form, however I am not the creator of the Word document, only the creator of the DB App that will capture it. I wil do a little followup tomorrow morning and repost.

What is the nature of the data to be captured: sentences? phrases? numbers?
At this time I believe it will be Date-Time and Numeric data with possibly some single words, and maybe a sentence to two, (the whole gambit).

What application do you want to have control to pull off this transfer?
I was thinking MS Access.

Thanks for the help, Carey
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrianWrenCommented:
If I read you right, what you are describing is one of the hardest-of-the-hard to do.  You essentially have to teach Access to read.  The information is not always in the same place, might not be in the same order etc.

If you have access to the creator of the word doc, investigate the possibility of setting up a form of standardization.  I'm not saying that you need at this point to figure out what that standardization will consist of, just ascertain whether there is an avenue of communication there.

Look forward to your additional post.

Brian
0
CareyMBilyeuAuthor Commented:
Edited text of question.
0
CareyMBilyeuAuthor Commented:
Adjusted points to 150
0
CareyMBilyeuAuthor Commented:
cquinn, do you have any samples of what you reference above?
0
blakeh1Commented:
You may be better off using FormFields in the Word doc, these will be much easier to get than Bookmarks. also with these fields you can validate the data in them which will cause less problems in the transfer.
here is a sample
Sub GetWordInfo()
    Dim rs As Recordset
    Dim wrd As Object
    Dim doc As Object
    Dim fld As Object
    Set rs = CurrentDb.OpenRecordset("General Table")
    Set wrd = CreateObject("Word.Application")
    Set doc = wrd.Documents.Open("C:\data\word97\data.doc")
   
    rs.AddNew
    For Each fld In doc.FormFields
        rs(fld.name) = fld.Result
    Next
    rs.Update
   
    rs.Close
    doc.Close
    wrd.Quit
    Set rs = Nothing
    Set doc = Nothing
    Set wrd = Nothing
End Sub

In word I have given the bookmark name for the field the same name as the field in the access table, this makes it easier, otherwise you'd have to do something like
rs("Fieldname")=doc.formfields("Name").result

0
blakeh1Commented:
Here is the same idea as above but htis time the routine is run from word, can be called from a button or the save event for the doc

Sub testFields()
    Dim wrksp As Object
    Dim db As Object
    Dim rs As Object
    Dim fld As FormField
    On Error GoTo err_testFields
    Set wrksp = CreateObject("DAO.DBENGINE")
    Set db = wrksp.opendatabase("C:\data\access97\Generaltbl.mdb")
    Set rs = db.openrecordset("General Table")
    rs.addnew
    For Each fld In ActiveDocument.FormFields
        rs(fld.Name) = fld.Result
    Next
    rs.Update
exit_testFields:
    On Error Resume Next
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Set wrksp = Nothing
    Exit Sub
err_testFields:
    MsgBox Err.Number & " : " & Err.Description
    Resume exit_testFields
End Sub
0
blakeh1Commented:
If you want to use bookmarks here is an access example. Agin the bookmarks are named the same as the fields in the access table.
Sub GetMyBookmarkInfo()
    Dim txt As String
    Dim brkmrk As object
    Dim rs As Recordset
    Dim wrd As Object
    Dim doc As Object
    Dim fld As Object
    Set rs = CurrentDb.OpenRecordset("General Table")
    Set wrd = CreateObject("Word.Application")
    Set doc = wrd.Documents.Open("C:\data\word97\data.doc")
     
    rs.AddNew    
    For Each brkmrk In doc.Bookmarks
        With brkmrk.Range
        .MoveEnd unit:=wdParagraph
        .Select
        End With
        rs(brkmrk.name) = Selection.Text
    Next
    rs.Update
    rs.close

 
    doc.Close
    wrd.Quit
    Set rs = Nothing
    Set doc = Nothing
    Set wrd = Nothing

End Sub

From the word side:

Sub GetMyBookmarkInfo()
    Dim txt As String
    Dim brkmrk As Bookmark
    For Each brkmrk In ActiveDocument.Bookmarks
        MsgBox brkmrk.Name
        With brkmrk.Range
        .MoveEnd unit:=wdParagraph
        .Select
        End With
        txt = Selection.Text
    MsgBox "Bookmark text is " & txt
    Next
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.