Hi,
I've been following the info from this tutorial: http://msdn.microsoft.com/en-us/library/aa155434(office.10).aspx to create an access database which can automatically import data from microsoft word forms.
I've been running into problems as I'm using Office 2010 whereas the tutorial is designed for Office 2000. Below is the code for the module to gather the info from Word and put it into Access.
The code is generating an error when trying to load up the Access database. Access 2010 databases dont use the extension .mdb anymore so i tried replacing this with .accdb but I get the error- unrecognised database format.
If anyone knows of how I would go about sorting this error out i'd really appreciate the help, it would be great to know if there are any other obvious lines of code that would generate errors because of the changes in Access 2010. As you can probably tell i'm new to all of this so if there are any updated or more streamlined ways of goinng about this it would be great to know!
Many Thanks,
Will
I've been following the info from this tutorial: http://msdn.microsoft.com/en-us/library/aa155434(office.10).aspx to create an access database which can automatically import data from microsoft word forms.
I've been running into problems as I'm using Office 2010 whereas the tutorial is designed for Office 2000. Below is the code for the module to gather the info from Word and put it into Access.
Code:
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
The code is generating an error when trying to load up the Access database. Access 2010 databases dont use the extension .mdb anymore so i tried replacing this with .accdb but I get the error- unrecognised database format.
If anyone knows of how I would go about sorting this error out i'd really appreciate the help, it would be great to know if there are any other obvious lines of code that would generate errors because of the changes in Access 2010. As you can probably tell i'm new to all of this so if there are any updated or more streamlined ways of goinng about this it would be great to know!
Many Thanks,
Will