Import Data Directly from Word Forms to Access Tables

Bergo

Registered User.
Local time
Today, 18:41
Joined
Jun 1, 2009
Messages
13
Hello,

I have followed the msdn Healthcare example on importing data from word into an access table (link below). However, when I run the code, i get the following error.. -2147467259: Could not use" ; file already in use.

The only difference in the code is the folder location and the mdb location, and i updated the code to reflect the correct path.

Anyone know how to resolve this error?

Thank you! :)
 
This sounds like the Word document is still open. Try closing it and try the import again.
 
Hello,

I have followed the msdn Healthcare example on importing data from word into an access table (link below). However, when I run the code, i get the following error.. -2147467259: Could not use" ; file already in use.

The only difference in the code is the folder location and the mdb location, and i updated the code to reflect the correct path.

Anyone know how to resolve this error?

Thank you! :)
I don't see any link. What code are you using?
 
Here's the link and code. The word document was also closed when I ran the code. Thanks again!

http://msdn.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
 
Hello,

I was able to resolve the error, which occurred because Outlook was open and I had a draft email in Word. Thanks for looking into it though. Have a nice day. :)
 
One more thing to be aware of -

If you are connecting to the database where your code is running - you don't need this:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

and you shouldn't use it. You should use

cnn = CurrentProject.Connection

instead, otherwise it will give you problems as well.
 
Hello,

Can InfoPath be used in place of Word in this example? I have created a InfoPath form that mirrors the Word Form, but now I am not sure on how to update the code.

Please advise :o

http://msdn.microsoft.com/en-us/library/aa155434(office.10).aspx

Sub GetWordData()
Dim appInfoPath As Info.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:\Documents and Settings\mwdosber\Desktop\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:\Documents and Settings\mwdosber\Desktop\" & _
"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("fldPhoneNumber").Result
!SocialSecurity = doc.FormFields("fldSocialSecurity").Result
!Gender = doc.FormFields("fldGender1").Result
!BirthDate = doc.FormFields("fldBirth").Result
!AdditionalCoverage = _
doc.FormFields("fldAdditional1").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
 
I'm sure you can use automation with InfoPath, but it may take some doing to find some people who have been doing it as it is still a relatively new technology/program.

And one more time I will ask you -

Is this code actually being run from Healthcare Contracts.mdb or is it from a different mdb file? If this code is in the Healthcare Contracts.mdb then you do not want to set the ADO connection string the way you are doing. You use CurrentProject.Connection instead or else you will end up with connection errors (too many connections, etc.).
 
Thanks!

The code is being run from the HealthCare Contract.mdb and I updated the code, but then I receive the following error msg.

"3704:Operation is not allowed when the object is closed"

When the original code is left in the script, it runs without error.

Thoughts?
 
Thanks!

The code is being run from the HealthCare Contract.mdb and I updated the code, but then I receive the following error msg.

"3704:Operation is not allowed when the object is closed"

When the original code is left in the script, it runs without error.

Thoughts?
It is so hard to tell you what is happening when you say "I updated the code, but then I receive the following error msg." but you don't give us the actual code you used which got the error. The problem is that we can't read minds or see exactly what you did. If you didn't do it right, I have no way of telling. You can say you did what I said to do but without seeing, you might have put it just slightly off and that is why you got the error.

Second, the original code DOES NOT RUN WITHOUT ERROR - You said so yourself - you said:
-2147467259: Could not use" ; file already in use
 
Sorry about that.. Here's the code I am working with now. I am attempting to pull in data from one word doc into 2 different access tables (tblContracts & tblSecurity), which are both located in the same db.

I am receiving the following error.. "3705: Operation is not allowed when the object is open"

Thoughts? Thanks!

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:\Documents and Settings\mwdosber\Desktop\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:\Documents and Settings\mwdosber\Desktop\" & _
"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
End With
rst.Open "tblSecurity", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Security = doc.FormFields("fldSecurity").Result
!Notes = doc.FormFields("fldNotes").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
 
okay, change this part:
Code:
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:\Documents and Settings\mwdosber\Desktop\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)
[B][COLOR=red]Set cnn = CurrentProject.Connection[/COLOR][/B]
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
 
Made the change (code below), but still getting the error "3705: Operation is not allowed when the object is open" The only file open is the mdb.

I really appreciate your 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:\Documents and Settings\mwdosber\Desktop\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)
Set cnn = CurrentProject.Connection
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

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
End With
rst.Open "tblSecurity", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Security = doc.FormFields("fldSecurity").Result
!Notes = doc.FormFields("fldNotes").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
 
In your current code I see you trying to open the recordset twice in a row. Is that really there or is it a typo here?

Set cnn = CurrentProject.Connection
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
 
What happens if you change this:

rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

to this:

rst.Open "tblContracts", cnn, _
adOpenDynamic, adLockOptimistic
 
I get a Compile error: Syntax error


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:\Documents and Settings\mwdosber\Desktop\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)
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
!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

End With

rst.Open "tblSecurity", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Security = doc.FormFields("fldSecurity").Result
!Notes = doc.FormFields("fldNotes").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
 
Why are there asterisks around adOpenDynamic? Those should not be there
This:
rst.Open "tblContracts", cnn, *adOpenDynamic*, adLockOptimistic
Should be this:


rst.Open "tblContracts", cnn, adOpenDynamic, adLockOptimistic
 
Hi SOS,

I removed the **, but i am still receiving this error, "3705: Operation is not allowed when the object is open"

The only file open is the mdb.


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:\Documents and Settings\mwdosber\Desktop\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)
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
!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

End With

rst.Open "tblSecurity", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Security = doc.FormFields("fldSecurity").Result
!Notes = doc.FormFields("fldNotes").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
 
And if you disable the error handling, which line does it highlight?
 

Users who are viewing this thread

Back
Top Bottom