Import data from Word Forms into Access (1 Viewer)

Z

zcwaa22

New member
Local time
Today, 13:36
Joined
Oct 8, 2010
Messages
1
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.

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
 
J

jhumphrey79

New member
Local time
Today, 07:36
Joined
Apr 14, 2012
Messages
7
I will be following this closely as I have the same need to import data into an Access table which data is in a Word doc.
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
I'm having the same issue.
My thoughts were to:
1. replace the .mdb with .accdb, add a new reference for Microsoft ActiveX Data Objects #.# Library and then change the Microsoft.Jet.OLEDB.4.0; or
2. if using the current database replace
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
with Set cnn = CurrentProject.Connection

I'm getting stuck on the strDocName resulting in runtime error 4605 but these suggestions may help others.

If anyone else has other ideas I'd love to try them
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
The code is written to run in any Office application. You are working inside the database so there is no need to use the ADO connection and recordset to the database.

Simply open an ordinary DAO Recordset instead of the ADO.

CurrentDb.OpenRecordset "whatever"
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
thanks for your reply. So what would you change it in the following:


Sub GetWordData2()
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

strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the saved contract " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = doc.Documents.Open(strDocName)
Set cnn = CurrentProject.Connection

rst.Open "tblContracts", cnn, _
adOpenDynamic, 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
!PostCode = doc.FormFields("fldPostCode").Result
!Phone = doc.FormFields("fldPhone").Result
!ANB = doc.FormFields("fldABN").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!Additional = doc.FormFields("fldAdditional").Result
.Update
rst.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

GoTo Cleanup
End Sub
 
thechazm

thechazm

VBA, VB.net, C#, Java
Local time
Today, 08:36
Joined
Mar 7, 2011
Messages
515
By using DAO in the database you will need to change a few sections of your code but start with changing

Code: 
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

To

Code: 
Dim db as database, rs as dao.recordset

That will get you started but your going to need to change your connection meathod and a few others to support using dao.
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
I've altered the code a little as suggested but it seems to be getting stuck on the strDocName section. Run-time error 91: Object variable or With block variable not set

The document is saved as a .doc document in the correct location. Any suggestions?


Sub GetWordData2()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim db As Database, rs As dao.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the saved contract " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = doc.Documents.Open(strDocName)

CurrentDb.OpenRecordset "tblContracts"


With rs
.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
!PostCode = doc.FormFields("fldPostCode").Result
!Phone = doc.FormFields("fldPhone").Result
!ABN = doc.FormFields("fldABN").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!Additional = doc.FormFields("fldAdditional").Result
.Update
rs.Close
End With

doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rs = Nothing
Set db = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

GoTo Cleanup
End Sub
 
HiTechCoach

HiTechCoach

Well-known member
Local time
Today, 07:36
Joined
Mar 6, 2006
Messages
4,357
Try changing this line

Code: 
Set doc = doc.Documents.Open(strDocName)


to

Code: 
Set doc = appWord.Documents.Open(strDocName)
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
Code: 
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

However this construct requires that Word is already open.

You probably want to create an instance of Word and then add to its Documents collection.

Code: 
Set appWord = CreateObject("Word.Application")
Set doc = appWord.Documents.Open(strDocName)
appWord.Visible = True ' if you want to see the session in Word
 
HiTechCoach

HiTechCoach

Well-known member
Local time
Today, 07:36
Joined
Mar 6, 2006
Messages
4,357
zcwaa22 said:
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.

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
Click to expand...



If the database is in the new ACE ( .accdb) format you will also need to change another connection string parameter beside the .mdb to .accdb. You will need to change the driver from JET to ACE (Microsoft.ACE.OLEDB.12.0)

Example connecting to an ACE (.accdb) database:

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


Also see: http://www.connectionstrings.com/providers/ace-oledb-12-0
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
Thanks guys, think I'm nearly there.
Current rst doesnt seem as though it's defined. This might be the current issue. (Object variable or With variable not set).
How would I fix this part?


Private Sub btnImport_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim db As Database
Dim strDocName As String
Dim rst As dao.Recordset
Dim blnQuitWord As Boolean

'On Error GoTo ErrorHandling

strDocName = "E:" & _
InputBox("Enter the name of the Applicant " & _
"you want to import:", "Import Applicant")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

CurrentDb.OpenRecordset "LegalEntityID"

With rst
.AddNew
!ApplicantName = doc.FormFields("ApplicantName").Result
!TradingName = doc.FormFields("TradingName").Result
!ABN = doc.FormFields("ABN").Result
!ACN = doc.FormFields("ACN").Result
!EntityType = doc.FormFields("EntityType").Result
!EntityType2 = doc.FormFields("EntityType2").Result
!BusAddress = doc.FormFields("BusAddress").Result
!BusSuburb = doc.FormFields("BusSuburb").Result
!BusState = doc.FormFields("BusState").Result
!BusPostCode = doc.FormFields("BusPostCode").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit

MsgBox "Applicant Imported!"
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
Replace:
CurrentDb.OpenRecordset "LegalEntityID"

with:
rst = CurrentDb.OpenRecordset("LegalEntityID")

BTW Where does blnQuitWord come from?
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
Thanks but it doesn't like your replacement line.
"rst = " is an invalid use of property

The code has come from:
msdn.microsoft.com/en-us/library/office/aa155434(v=office.10).aspx
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
Sorry I wasn't paying enough attention.

Set rst = CurrentDb.OpenRecordset("LegalEntityID")
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
That works perfectly. Thanks.
The problem I have now is that I need to import data from the form into multiple tables.
The first table will have a 'key' created automatically for the new record but how would I link the next table to the newly created key?
This is where I'm up to. The problem, I imagine, will be the first coupe lines of the 2nd With..

Private Sub btnImport_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim db As Database
Dim strDocName As String
Dim rst As dao.Recordset
Dim rst1 As dao.Recordset
Dim blnQuitWord As Boolean

'On Error GoTo ErrorHandling

strDocName = "E:" & _
InputBox("Enter the name of the Applicant " & _
"you want to import:", "Import Applicant")

Set appWord = CreateObject("Word.Application")
Set doc = appWord.Documents.Open(strDocName)
appWord.Visible = True


Set rst = CurrentDb.OpenRecordset("LegalEntityID")
Set rst1 = CurrentDb.OpenRecordset("Contacts")


With rst
.AddNew
!ApplicantName = doc.FormFields("ApplicantName").Result
!TradingName = doc.FormFields("TradingName").Result
!ABN = doc.FormFields("ABN").Result
!ACN = doc.FormFields("ACN").Result
!EntityType = doc.FormFields("EntityType").Result
!EntityType2 = doc.FormFields("EntityType2").Result
!BusAddress = doc.FormFields("BusAddress").Result


.Update

End With

With rst1
!Key = "select max(key) from legalentityid;"
!BDName = doc.FormFields("BDName").Result
!BDPosition = doc.FormFields("BDPosition").Result
.Update
.Close


End With
'End If
'cmd.Run

doc.Close
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
Read the key field from the first recordset into a variable after the record is updated.
Write that value into the second recordset.

BTW Recordsets have a LastUpdated property which can be useful but you don't really need that here.
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
What would be the best way to do this? Dlookup?

Error is "object required"

Any suggestions?
Thanks


Dim currentkey As Field
currentkey = DLookup("key", "legalentityid", LegalEntityID.ApplicantName = doc.FormFields("ApplicantName").Result)


With rst1
.AddNew
!Key = "currentkey
!BDName = doc.FormFields("BDName").Result
!BDPosition = doc.FormFields("BDPosition").Result
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:36
Joined
Jan 20, 2009
Messages
12,851
You already have rst open against legalentityid so there is no need for the DLookup

Code: 
Dim currentkey as Long
currentkey = rst!key

The problem with your DLookup is the Dim As Field because that is the field object itself. You are just reading a value which will be a Long if it is autonumbered.)
 
S

Steve400

Registered User.
Local time
Today, 22:36
Joined
May 1, 2013
Messages
33
That make's sense but now if I close the first table with a .close I get the error: "Object invalid or no longer set"

And if I remove the .close then I get: "The document you selected does not contain the required form fields"

I cant win...

!RCSSubmission = "Yes"
.Update
.Close
End With

Set rst1 = CurrentDb.OpenRecordset("Contacts")

Dim currentkey As Long
currentkey = rst!Key

With rst1
.AddNew
!Key = currentkey
!BDName = doc.FormFields("BDName").Result
 
S

ShawnJohnson

New member
Local time
Today, 06:36
Joined
Jun 12, 2014
Messages
5
I have a similar issue, using a copy of this code with modifications, i keep getting an error 432 File name or class name not found during Automation operation. NOTE I am not a programmer, have only dabbled.....

the code is shown below....any help is appreciated. If I F8 and step through the code it reports the error at the End Select line near the bottom.


Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim db As Database
Dim strDocName As String
Dim rst As dao.Recordset
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\Users\vb020315\Documents\PQRs\PQRs" & _
InputBox("Enter the name of the Word report " & _
"you want to import:", "Import Report")

Set appWord = GetObject("C:\Users\vb020315\Documents\PQRs\PQRs\1.doc", "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
appWord.Visible = True

Set rst = CurrentDb.OpenRecordset("Reports")

With rst
.AddNew
!Make = doc.FormFields("Make").Result
!Model = doc.FormFields("Model").Result
!Title = doc.FormFields("Title").Result
!ReportType = doc.FormFields("ReportType").Result
!Year = doc.FormFields("Year").Result
!BodyStyle = doc.FormFields("BodyStyle").Result
!Engine = doc.FormFields("Engine").Result
!Cases = doc.FormFields("Cases").Result
!VIN1 = doc.FormFields("VIN1").Result
!km1 = doc.FormFields("km1").Result
!Eng1 = doc.FormFields("Eng1").Result
!Trans1 = doc.FormFields("Trans1").Result
!Paint1 = doc.FormFields("Paint1").Result
!VIN2 = doc.FormFields("VIN2").Result
!km2 = doc.FormFields("km2").Result
!Eng2 = doc.FormFields("Eng2").Result
!Trans2 = doc.FormFields("Trans2").Result
!Paint2 = doc.FormFields("Paint2").Result
!VIN3 = doc.FormFields("VIN3").Result
!km3 = doc.FormFields("km3").Result
!Eng3 = doc.FormFields("Eng3").Result
!Trans3 = doc.FormFields("Trans3").Result
!Paint3 = doc.FormFields("Paint3").Result
!LabourOp = doc.FormFields("LabourOp").Result
!DrivingConditions = doc.FormFields("DrivingConditions").Result
!Environment = doc.FormFields("Environment").Result
!Concern = doc.FormFields("Concern").Result
!Cause = doc.FormFields("Cause").Result
!Correction = doc.FormFields("Correction").Result
!Success = doc.FormFields("Success").Result
!AdditionalVINs = doc.FormFields("AdditionalVINs").Result
!PartNo = doc.FormFields("PartNo").Result
!PartsName = doc.FormFields("PartsName").Result
!PartsAvailable = doc.FormFields("PartsAvailable").Result
!Originator = doc.FormFields("Originator").Result
!OrininatorEmail = doc.FormFields("OrininatorEmail").Result
!DealerCode = doc.FormFields("DealerCode").Result
!Date = doc.FormFields("Date").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Report 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

thanks in advance
Shawn
 
You must log in or register to reply here.

Similar threads

S
Creating a Barcode and copy into Word Document
Replies
7
Views
387
GPGeorge
GPGeorge
G
How to fix bookmarks in word does not work in win 11 / o365?
Replies
10
Views
787
arnelgp
arnelgp
S
Rich Text into Word document
Replies
13
Views
1,037
stu_c
S
L
Second instance of Word opened from Access arises security warning?
Replies
14
Views
645
lauro
L
R
  • Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2
Replies
32
Views
900
spaLOGICng
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom