• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3120
  • Last Modified:

Access 2007 Module Code to Import Word Doc Fields

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!
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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: http://www.connectionstrings.com/?carrier=access2007

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 ...
fishrichAuthor Commented:
k than you, i will give that a try and reply with my results
Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now