Access 2007 Module Code to Import Word Doc Fields

Posted on 2007-10-19
Last Modified: 2008-03-23
First off I just want to start off by thanking all of the experts for sharing their knowledge!!! You guys really make this the best page to come for research&.  Now on to my issue..
I would consider myself an experienced novice user with Access and here is my problematic scenario:
What I am trying to do is have my users fill out a word document form, and then using a module import the fields into multiple tables in my DB.
I created a Word Form 2003 type, and created the fields in it with matching names the multiple table fields names.
I have built a db in MS ACCESS 2007 and the structure is as such: TABLE1, TABLE2, TABLE3&  With fields as such: TABLE1 FIELDA FIELDB FIELDC; TABLE2 FIELD1 FIELD2 FIELD3, TABLE3 FIELD* FIELD** FIELD***
I need assistance with the proper code to create a module to import the info from the Word Doc fields into the multiple Tables in the DB.
I have tried to use something along these lines but obviously the code is old and the syntax is for a totally different version of the Jet engine.  I think this would be a good starting ground, but I dont know how to code and so I ask the experts for help!!!
Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocName = "C:\Contracts\" & _
      InputBox("Enter the name of the Word contract " & _
      "you want to import:", "Import Contract")
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\My Documents\" & _
      "Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
      adOpenKeyset, adLockOptimistic
With rst
      !FirstName = doc.FormFields("fldFirstName").Result
      !LastName = doc.FormFields("fldLastName").Result
      !Company = doc.FormFields("fldCompany").Result
      !Address = doc.FormFields("fldAddress").Result
      !City = doc.FormFields("fldCity").Result
      !State = doc.FormFields("fldState").Result
      !ZIP = doc.FormFields("fldZIP1").Result & _
            "-" & doc.FormFields("fldZIP2").Result
      !Phone = doc.FormFields("fldPhone").Result
      !SocialSecurity = doc.FormFields("fldSocialSecurity").Result
      !Gender = doc.FormFields("fldGender").Result
      !BirthDate = doc.FormFields("fldBirthDate").Result
      !AdditionalCoverage = _
End With
If blnQuitWord Then appWord.Quit
MsgBox "Contract Imported!"
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
Select Case Err
Case -2147022986, 429
      Set appWord = CreateObject("Word.Application")
      blnQuitWord = True
      Resume Next
Case 5121, 5174
      MsgBox "You must select a valid Word document. " _
            & "No data imported.", vbOKOnly, _
            "Document Not Found"

Case 5941
      MsgBox "The document you selected does not " _
            & "contain the required form fields. " _
            & "No data imported.", vbOKOnly, _
            "Fields Not Found"
Case Else
      MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub

Thank you for you help!
Question by:fishrich
    LVL 84

    Accepted Solution

    First: Are you doing this from inside Access? If so, you don't need to open a connection ... if you are NOT doing this from within Access, then you'd need to change the connection to Access (assuming you're using the new ACCDB format, that is). Here's the basic syntax:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

    From here:

    So you'd change your code like this:

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=C:\My Documents\Healthcare Contracts.mdb;"

    This should open your connection ...
    LVL 2

    Author Comment

    k than you, i will give that a try and reply with my results
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now