ADO not Importing >255 charactors.

[xlStar]

I am trying to Import data from Word Form into Access. One Access's column is named "Narrative" and Data Type is set to Memo.

The Word Form maximum length is 1000 for this same field.

However, the ADO VBA code is only importing 255 data whereas this field on Word has 372. How can my code below import all 372 data...

With rst
.AddNew
!Item = doc.FormFields("Text1").Result
!Portfolio = doc.FormFields("Text2").Result
!Department = doc.FormFields("Text3").Result
!Directorate = doc.FormFields("Text4").Result
!ServiceArea = doc.FormFields("Text5").Result
!Title = doc.FormFields("Text6").Result
!Narrative = doc.FormFields("Text7").Result
!CouncilPlanTheme = doc.FormFields("Text8").Result
!CouncilPlanThemeRef = doc.FormFields("Text9").Result
!PrimaryDriver = doc.FormFields("Text10").Result
!PrimaryDriverRef = doc.FormFields("Text11").Result
!SupportingInformation = doc.FormFields("Text12").Result
.Update
.Close
End With

The affected one is...!Narrative = doc.FormFields("Text7").Result

 

[HarleyQuinn]

In the ADO recordset, what type of field is Narrative?

How are you creating the recordset? What is the DefinedSize property for Narrative?



HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[xlStar]

The table is already set up with 12 fields, one of them is called "Narrative" as is set to "Memo".

The VBA is inserting records from MS Word. It all working fine apart from that I want "Narrative" to accept >255 field size as one record in MS Word is 372 charactors long.


I have manage to find an alternative way by creating a new column called Narrative2 and amend Narrative to Narrative1 and set them both to TEXT with Field Size of 255. Amendment to my VBA is...

!Narrative1 = Mid(doc.FormFields("Text7").Result, 1, 250)
!Narrative2 = Mid(doc.FormFields("Text7").Result, 251, 250)



...is there a better way?

 

[HarleyQuinn]

So, in response to my original question, you're creating the recordset from the table? Could you show us how you're creating it?

The two other questions I posted relating to the recordset will probably be answered once we've seen the code you're using to create/populate the recordset.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[xlStar]

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 = "U:\CustomerSupport\Returns\" & _
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=U:\CustomerSupport\Test Word Form Connection.mdb;"

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

With rst
.AddNew
!Item = doc.FormFields("Text1").Result
!Portfolio = doc.FormFields("Text2").Result
!Department = doc.FormFields("Text3").Result
!Directorate = doc.FormFields("Text4").Result
!ServiceArea = doc.FormFields("Text5").Result
!Title = doc.FormFields("Text6").Result
!Narrative1 = Mid(doc.FormFields("Text7").Result, 1, 250)
!Narrative2 = Mid(doc.FormFields("Text7").Result, 251, 250)
!CouncilPlanTheme = doc.FormFields("Text8").Result
!CouncilPlanThemeRef = doc.FormFields("Text9").Result
!PrimaryDriver = doc.FormFields("Text10").Result
!PrimaryDriverRef = doc.FormFields("Text11").Result
!SupportingInformation = doc.FormFields("Text12").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Document 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

 

[HarleyQuinn]

Hmm, strange. In testing I can get 372 characters into the Narrative field (set to Memo) no problem at all.

Using:

Sub GetWordData()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=U:\CustomerSupport\Test Word Form Connection.mdb;"
    
rst.Open "tblA", cnn, _
    adOpenKeyset, adLockOptimistic

Do While Not rst.EOF And Not rst.BOF
    Debug.Print Len(rst.Fields("Narrative"))
    Debug.Print rst.Fields("Narrative").DefinedSize
    Debug.Print rst.Fields("Narrative").ActualSize
    rst.MoveNext
Loop

With rst
    .AddNew
    !Narrative = String$(372, "A")
    .Update
    .Close
End With
If rst.State <> adStateClosed Then rst.Close
cnn.Close
MsgBox "Document Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrorHandling:

    MsgBox Err & ": " & Err.Description
    
GoTo Cleanup
End Sub

I can't however test this fully as I can't seem to get 372 characters in a formfield in Word...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com