Word & ADO

[biot]

hi
i have and ado recordset,and i'm trying to generate a report using ms word.
the problem is that the report processing is too low, and if i'm trying to generate 2 reports i get windows message of system low resources.
what can i do to speed up word's process,and how can i avoid the low resources message?

[Raptors Fan]

I suggest that you consider doing your reports right in VB... try using data reports.

RF

[Cimperiali]

Speed up may or may not be possible, but it will be rather difficult to help enginering without having a look at your code...

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood,
Bruno Paris and all the other wonderful people who made and make Codeguru
a great place.
Come back soon, you Gurus.

The Rater

[jaizn]

Hi,

Why are you using word to report? I use ActiveReports which is very good and supremely flexible.

If you're using word so as to produce Word documents (a valid reason) then you will probably find it impossible. I did this a couple of years ago and experimented with OLE and COM as the manipulation method but both took too long to do even simple reports.

The solution I came up with was to use Rich Text Format (RTF) directly.

Its quite hard (at least I found it so) but given time you can do anything you want with the Word formatting structures simply using text manipulation. Compared to other mark up languages (HTML) its a bit of a black art. A good way to get started is to create simple documents using WordPad, save as RTF and then edit with Notepad.

I had a look on the net and you can get a copy of the RTF 1.5 specification at the following address: http://www.cena.dgac.fr/~sagnier/info/formats/rtf/rtfspe15.htm.

From what I remember I managed to get a Word file that was taking 2.5 minutes to produce using COM maipulation to be written from database to disk in less than a second. Then I simply opened it up using word.


Jai.

[biot]

set appWRD = CreateObject("Word.Application")
appWRD.Options.CheckGrammarAsYouType = false
appWRD.Options.CheckSpellingAsYouType = false
appWRD.Options.Pagination = false
set docWRD = appWRD.Documents.Add(App.Path + "\List.dot")
rsCD.MoveFirst
While Not rsCD.EOF
With docWRD.Tables(1).Rows.Last
.Range.Font.Size = 14
.Cells(3).Range.Text = rsCD.Fields("CD_Name").Value
.Cells(2).Range.Text = rsCD.Fields("CD_Num").Value
.Cells(1).Range.Text = rsCD.Fields("CD_Remark").Value
docWRD.Tables(1).Rows.Add
End With
let txtProcess.Text = rsCD.Fields("CD_Name").Value & " (" & Counter & "/" & rsCD.RecordCount & ")"
let Counter = Counter + 1
rsCD.MoveNext
Wend

[biot]

datareports doesn't support multi language (the reports comes out missunderstood when using hebrew and english)

[Cimperiali]

'have a try with tis (you will have to adjust code)

Dim cnCd as ADODB.Connection
Dim rsCD as ADODB.Recordset
'eraly binding: put a reference to
'Microsoft word 8.0 or 9.0 object library
Dim appWRD as Word.Application
Dim docWRD as Word.Document

private Sub Command1_Click()
Dim lngCounter as Long
Dim TotRecord as Long
Dim arr_RSCD as Variant
set appWRD = new Word.Application 'this is faster than createobject
appWRD.Options.CheckGrammarAsYouType = false
appWRD.Options.CheckSpellingAsYouType = false
appWRD.Options.Pagination = false
set docWRD = appWRD.Documents.Add(App.Path + "\List.dot")

'decide where to put the table
set myRange = ActiveDocument.Range(0, 0)
'set the font once and for all the table
myRange.Font.Size = 14 'did not test this: you may have to adjust
'create the whole table, with all the rows requested
'count your record with rsCD.RecordCount once only or each time it
'will require a new count, slowing down app
TotRecord = rsCD.RecordCount
ActiveDocument.Tables.Add Range:=myRange, NumRows:=rsCD.RecordCount, NumColumns:=3
'use an array which should be faster even if it is a variant
arr_RSCD = rsCD.GetRow
'you have all records in array arr_rscd. So
'close your recordset
rsCD.Close
'set it to nothing
set rsCD = nothing
'if you do not need, close the connection
'...
for lngCounter = 0 to TotRecord
With docWRD.Tables(1).Rows(lngCounter)
'it may be cols, row. In case:
'arr_RSCD( 2,lngCounter) instead of:
.Cells(3).Range.Text = arr_RSCD(lngCounter, 2) 'name is Third filed? rsCD.Fields("CD_Name").Value
.Cells(2).Range.Text = arr_RSCD(lngCounter, 1) 'rsCD.Fields("CD_Num").Value
.Cells(1).Range.Text = arr_RSCD(lngCounter, 0) 'rsCD.Fields("CD_Remark").Value
End With
'here you counted the whole records each time
let txtProcess.Text = arr_RSCD(lngCounter, 2) & " (" & lngCounter & "/" & TotRecord & ")"
let lngCounter = lngCounter + 1
next lngCounter
'if you do not need it more, destroy the array
arr_RSCD = nothing
'....at last (before closing your program, do not forget to:
'release reference to doc and word

docWRD.Close false'decide about saving it before this line
set docWord = nothing
appWRD.Documents.close
appWRD.quit
set appWRD = nothing
End Sub




Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Cimperiali]

1)Try putting a DoEvents statement inside the loop
2)Use a read-only, Forward-Only recordset
3)Remembre there is a limit in Word table lenght (do not know exactly how much, but it should be about 65.000 rows), and the more the rows, the more it will be slow. So it may be a good idea to split the table (put informations in more than one table) after, say, 100 rows have been written.


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater