Importing Word Form into Access.... part2

A

awrex

I'm using code (attached at the end) from a MSDN article to import Word form
data into a Access db.... I made a modification with the help of Mr Doug
Robbins on the open dialog box verus a input box for the file information.

Your help is appreciated!!!!!

The error that I'm getting is as follows:

-2147467259 The database has been placed in a state by user 'Admin' on
machine 'XXXXXXXX' that prevents is from being opened or locked.


Here is the 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:\Documents and Settings\chavira\Desktop\CPD\Status Reports\"
'InputBox("Enter the name of the Word PSR " & _
'"You want to import:", "Import PSR")
With Dialogs(wdDialogFileOpen)
If .Display = -1 Then
strDocName = .Name
End If
End With


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\chavira\My Documents\" & _
"CPD Form.mdb;"
rst.Open "CPD_Table", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!ProjName = doc.FormFields("ProjName").Result
!ProjMgr = doc.FormFields("ProjMgr").Result
!CovPer = doc.FormFields("CovPer").Result
!DateSub = doc.FormFields("DateSub").Result
!City = doc.FormFields("fldCity").Result
!ProjDesc = doc.FormFields("ProjDesc").Result
!Status = doc.FormFields("Status").Result
!StatExp = doc.FormFields("StatExp").Result
!InfoBrand = doc.FormFields("InfoBrand").Result
!InfoType = doc.FormFields("InfoType").Result
!InfoSize = doc.FormFields("InfoSize").Result
!InfoPhase = doc.FormFields("InfoPhase").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!DatesVdev = doc.FormFields("DatesVdev").Result
!DatesStag = doc.FormFields("DatesStag").Result
!DatesEst = doc.FormFields("DatesEst").Result
!DatesProd = doc.FormFields("DatesProd").Result
!SpptCPD = doc.FormFields("SpptCPD").Result
!SppTWM = doc.FormFields("SppTWM").Result
!SpptDE = doc.FormFields("SpptDE").Result
!ConTypeCPD = doc.FormFields("ConTypeCPD").Result
!ConTypeTWM = doc.FormFields("ConTypeTWM").Result
!ConTypeDE = doc.FormFields("ConTypeDE").Result
!ConAmCPD = doc.FormFields("ConAmCPD").Result
!ConAmTWM = doc.FormFields("ConAmTWM").Result
!ConAmDE = doc.FormFields("ConAmDE").Result
!ConVenCPD = doc.FormFields("ConVenCPD").Result
!ConVenTWM = doc.FormFields("ConVenTWM").Result
!ConVenDE = doc.FormFields("ConVenDE").Result
!ExecSum = doc.FormFields("ExecSum").Result
!AccWIP = doc.FormFields("AccWIP").Result
!PlnNW = doc.FormFields("PlnNW").Result
!Iss01 = doc.FormFields("Iss01").Result
!Iss01Desc = doc.FormFields("Iss01Desc").Result
!Iss01AI = doc.FormFields("Iss01AI").Result
!Iss01Act = doc.FormFields("Iss01Act").Result
!Iss01Asg = doc.FormFields("Iss01Asg").Result
!Iss01DD = doc.FormFields("Iss01DD").Result
!Iss01RSD = doc.FormFields("Iss01RSD").Result
!Iss01RSAT = doc.FormFields("Iss01RSAT").Result
!Iss02 = doc.FormFields("Iss02").Result
!Iss02Desc = doc.FormFields("Iss02Desc").Result
!Iss02AI = doc.FormFields("Iss02AI").Result
!Iss02Act = doc.FormFields("Iss02Act").Result
!Iss02Asg = doc.FormFields("Iss02Asg").Result
!Iss02DD = doc.FormFields("Iss02DD").Result
!Iss02RSD = doc.FormFields("Iss02RSD").Result
!Iss02RSAT = doc.FormFields("Iss02RSAT").Result
!Iss03 = doc.FormFields("Iss03").Result
!Iss03Desc = doc.FormFields("Iss03Desc").Result
!Iss03AI = doc.FormFields("Iss03AI").Result
!Iss03Act = doc.FormFields("Iss03Act").Result
!Iss03Asg = doc.FormFields("Iss03Asg").Result
!Iss03DD = doc.FormFields("Iss03DD").Result
!Iss03RSD = doc.FormFields("Iss03RSD").Result
!Iss03RSAT = doc.FormFields("Iss03RSAT").Result
!Iss04 = doc.FormFields("Iss04").Result
!Iss04Desc = doc.FormFields("Iss04Desc").Result
!Iss04AI = doc.FormFields("Iss04AI").Result
!Iss04Act = doc.FormFields("Iss04Act").Result
!Iss04Asg = doc.FormFields("Iss04Asg").Result
!Iss04DD = doc.FormFields("Iss04DD").Result
!Iss04RSD = doc.FormFields("Iss04RSD").Result
!Iss04RSAT = doc.FormFields("Iss04RSAT").Result
!Iss05 = doc.FormFields("Iss05").Result
!Iss05Desc = doc.FormFields("Iss05Desc").Result
!Iss05AI = doc.FormFields("Iss05AI").Result
!Iss05Act = doc.FormFields("Iss05Act").Result
!Iss05Asg = doc.FormFields("Iss05Asg").Result
!Iss05DD = doc.FormFields("Iss05DD").Result
!Iss05RSD = doc.FormFields("Iss05RSD").Result
!Iss05RSAT = doc.FormFields("Iss05RSAT").Result
!Iss06 = doc.FormFields("Iss06").Result
!Iss06Desc = doc.FormFields("Iss06Desc").Result
!Iss06AI = doc.FormFields("Iss06AI").Result
!Iss06Act = doc.FormFields("Iss06Act").Result
!Iss06Asg = doc.FormFields("Iss06Asg").Result
!Iss06DD = doc.FormFields("Iss06DD").Result
!Iss06RSD = doc.FormFields("Iss06RSD").Result
!Iss06RSAT = doc.FormFields("Iss06RSAT").Result
!Iss07 = doc.FormFields("Iss07").Result
!Iss07Desc = doc.FormFields("Iss07Desc").Result
!Iss07AI = doc.FormFields("Iss07AI").Result
!Iss07Act = doc.FormFields("Iss07Act").Result
!Iss07Asg = doc.FormFields("Iss07Asg").Result
!Iss07DD = doc.FormFields("Iss07DD").Result
!Iss07RSD = doc.FormFields("Iss07RSD").Result
!Iss07RSAT = doc.FormFields("Iss07RSAT").Result
!Iss08 = doc.FormFields("Iss08").Result
!Iss08Desc = doc.FormFields("Iss08Desc").Result
!Iss08AI = doc.FormFields("Iss08AI").Result
!Iss08Act = doc.FormFields("Iss08Act").Result
!Iss081Asg = doc.FormFields("Iss08Asg").Result
!Iss08DD = doc.FormFields("Iss08DD").Result
!Iss08RSD = doc.FormFields("Iss08RSD").Result
!Iss08RSAT = doc.FormFields("Iss08RSAT").Result
!Iss09 = doc.FormFields("Iss09").Result
!Iss09Desc = doc.FormFields("Iss09Desc").Result
!Iss09AI = doc.FormFields("Iss09AI").Result
!Iss09Act = doc.FormFields("Iss09Act").Result
!Iss09Asg = doc.FormFields("Iss09Asg").Result
!Iss09DD = doc.FormFields("Iss09DD").Result
!Iss09RSD = doc.FormFields("Iss09RSD").Result
!Iss09RSAT = doc.FormFields("Iss09RSAT").Result
!Iss10 = doc.FormFields("Iss10").Result
!Iss10Desc = doc.FormFields("Iss10Desc").Result
!Iss10AI = doc.FormFields("Iss10AI").Result
!Iss10Act = doc.FormFields("Iss10Act").Result
!Iss10Asg = doc.FormFields("Iss10Asg").Result
!Iss10DD = doc.FormFields("Iss10DD").Result
!Iss10RSD = doc.FormFields("Iss10RSD").Result
!Iss10RSAT = doc.FormFields("Iss10RSAT").Result
!Risk01 = doc.FormFields("Risk01").Result
!Risk01Desc = doc.FormFields("Risk01Desc").Result
!Risk01Prob = doc.FormFields("Risk01Prob").Result
!Risk01MI = doc.FormFields("Risk01MI").Result
!Risk01MD = doc.FormFields("Risk01MD").Result
!Risk02 = doc.FormFields("Risk02").Result
!Risk02Desc = doc.FormFields("Risk02Desc").Result
!Risk02Prob = doc.FormFields("Risk02Prob").Result
!Risk02MI = doc.FormFields("Risk02MI").Result
!Risk02MD = doc.FormFields("Risk02MD").Result
!Risk03 = doc.FormFields("Risk03").Result
!Risk03Desc = doc.FormFields("Risk03Desc").Result
!Risk03Prob = doc.FormFields("Risk03Prob").Result
!Risk03MI = doc.FormFields("Risk03MI").Result
!Risk03MD = doc.FormFields("Risk03MD").Result
!Risk04 = doc.FormFields("Risk04").Result
!Risk04Desc = doc.FormFields("Risk04Desc").Result
!Risk04Prob = doc.FormFields("Risk04Prob").Result
!Risk04MI = doc.FormFields("Risk04MI").Result
!Risk04MD = doc.FormFields("Risk04MD").Result
!Risk05 = doc.FormFields("Risk05").Result
!Risk05Desc = doc.FormFields("Risk05Desc").Result
!Risk05Prob = doc.FormFields("Risk05Prob").Result
!Risk05MI = doc.FormFields("Risk05MI").Result
!Risk05MD = doc.FormFields("Risk05MD").Result
!Risk06 = doc.FormFields("Risk06").Result
!Risk06Desc = doc.FormFields("Risk06Desc").Result
!Risk06Prob = doc.FormFields("Risk06Prob").Result
!Risk06MI = doc.FormFields("Risk06MI").Result
!Risk06MD = doc.FormFields("Risk06MD").Result
!Risk07 = doc.FormFields("Risk07").Result
!Risk07Desc = doc.FormFields("Risk07Desc").Result
!Risk07Prob = doc.FormFields("Risk07Prob").Result
!Risk07MI = doc.FormFields("Risk07MI").Result
!Risk07MD = doc.FormFields("Risk07MD").Result
!Risk08 = doc.FormFields("Risk08").Result
!Risk08Desc = doc.FormFields("Risk08Desc").Result
!Risk08Prob = doc.FormFields("Risk08Prob").Result
!Risk08MI = doc.FormFields("Risk08MI").Result
!Risk08MD = doc.FormFields("Risk08MD").Result
!Risk09 = doc.FormFields("Risk09").Result
!Risk09Desc = doc.FormFields("Risk09Desc").Result
!Risk09Prob = doc.FormFields("Risk09Prob").Result
!Risk09MI = doc.FormFields("Risk09MI").Result
!Risk09MD = doc.FormFields("Risk09MD").Result
!Risk10 = doc.FormFields("Risk10").Result
!Risk10Desc = doc.FormFields("Risk10Desc").Result
!Risk10Prob = doc.FormFields("Risk10Prob").Result
!Risk10MI = doc.FormFields("Risk10MI").Result
!Risk10MD = doc.FormFields("Risk10MD").Result
!Comm = doc.FormFields("Comm").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "PSR 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
 
R

Robert Morley

That would typically indicate that you're trying to open the database more
than once. You didn't by any chance have a copy of it open in design mode
somewhere else (same machine or a different one), did you?


Rob
 
A

awrex

Ok... rebooted and now it's a 2147217900: Invalid SQL statement expected
DELETE, INSERT, PROCEDURE, or UPDATE.. error...
 
C

chantal.lyn

Awrex,

re your original post, you need to split the database so that the code
can run.

I had the same problem, and in the end (with much help from Google
Groups threads!) I resorted to converting the word forms to text (as
the person who set up the Word template didn't bother to name most of
the formfields), importing the formfield contents, then appending the
data to tables with the fieldnames set up (because the Word forms were
in 3 different formats).

To convert to text:

Sub CompileFormData()
On Error Resume Next

Const strDir As String = "[filepath]"
Const strDirTest As String = strDir & "\converted"
Dim I As Integer
Dim Doc As Word.Document
Dim CurDoc As Word.Document
Dim TextDoc As Word.Document
Dim boolUserOpenFormat As Boolean
Dim appWord As Word.Application
'Dim appWord As Object
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

'On Error GoTo ErrorHandling
Set appWord = GetObject(, "Word.Application") ' get the
running instance

If Err.Number = 429 Or Err.Number = 91 Or Err.Number = 462 Then
Err.Clear
Set appWord = GetObject("", "Word.Application") ' not there,
create a New instance
ElseIf Err.Number > 0 Then
MsgBox Err.Description
Exit Sub
End If



'Set Doc = appWord.Documents.Open(strDocName)
Set cnn = New ADODB.Connection

boolUserOpenFormat = Options.ConfirmConversions


Set CurDoc = appWord.ActiveDocument


With Application.FileSearch
.NewSearch
.LookIn = strDir
.SearchSubFolders = False
If .Execute() > 0 Then
ChangeFileOpenDirectory strDirTest
For I = 1 To .FoundFiles.count
Set Doc = Documents.Open(.FoundFiles(I))
With Doc
.SaveFormsData = True
.SaveAs FileName:=Left(.Name, Len(.Name) - 4) &
".txt", _
FileFormat:=wdFormatText, SaveFormsData:=True
.Close
End With
Next I
End If
End With


With Application.FileSearch
.NewSearch
.LookIn = strDirTest
.SearchSubFolders = False
.FileName = "*.txt"
If .Execute() > 0 Then
Options.ConfirmConversions = False
For I = 1 To .FoundFiles.count
Set TextDoc = Documents.Open(.FoundFiles(I))
CurDoc.Range.InsertAfter TextDoc.Range.Text & vbCrLf
TextDoc.Close
Next I
Options.ConfirmConversions = boolUserOpenFormat
End If
End With

Set appWord = Nothing

---------------------------------------------
To import:

Public Sub ImportFiles()
On Error GoTo ImportError

strpath = "[filepath]\converted\"
strFile = Dir(strpath, vbNormal)

Do While strFile > ""
DoCmd.TransferText acImportDelim, import_text, "tblNew", strpath &
strFile, 0


strFile = Dir()
Loop


Exit Sub

ImportError:
MsgBox Err & ": " & Err.Description, vbInformation

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing File Name 4
Problem with Word Doc Import Loop 2
Importing Word form data to Access 3
Troubleshoot code - Spot deliberate mistake 1
Import data from Word to Access Tables 1
Module For Importing Word Forms 4
Import data from Word using a form 1
error "out of present range" 0

Top