[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 Module Code to Import Word Doc Fields

Posted on 2007-10-19
4
Medium Priority
?
3,108 Views
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!!!
http://msdn2.microsoft.com/en-us/library/aa155434(office.10).aspx
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
      .AddNew
      !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 = _
            doc.FormFields("fldAdditional").Result
      .Update
      .Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
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!
0
Comment
Question by:fishrich
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 20114586
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 ...
0
 
LVL 2

Author Comment

by:fishrich
ID: 20227184
k than you, i will give that a try and reply with my results
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21189595
Forced accept.

Computer101
EE Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

831 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