On Tue, 16 Jun 2009 19:52:49 -0700 (PDT), Bob Alston
I have a State goverment defined Word document with LOTS of form
fields, spanning 26 pages. Probably 1000 fields as a good guess. The
data is in an Access database in 12 different tables, each table
corresponding to one or m ore pages.
I have completed the load of data via DAO into the first page, about
50 fields. It seems rather slow.
My code tends to follow this format for assignments into the form
fields:
If inrec.Fields("1LocAccessHome") Then ActiveDocument.FormFields
("LocAccessHome").CheckBox.Value = True
ActiveDocument.FormFields("mentaltexta").Result = inrec.Fields
("1mentaltexta")
Everything seems to work it is just a lot of assignments and in tests
seems to take several seconds for this one page. Some of the later
pages are even more complex swith many more form fields.
I am planning to store the macro in the word document itself rather in
the Normal dot.
Any suggestions to speed up the processing?
Bob Alston
bobalston9 AT yahoo D O T com
I should have added that I am already aware of the benefits of not
having the document visible when doing such updates and only make it
visible once the updates are complete.
There isn't a lot you can do. One thing that may have a (probably
minor) effect is to use the With ... End With construct to eliminate
one layer of pointer derefencing, like this:
With ActiveDocument.FormFields
If inrec.Fields("1LocAccessHome") Then _
.Item("LocAccessHome").CheckBox.Value = True
.Item("mentaltexta").Result = _
inrec.Fields ("1mentaltexta")
End With
The .Item method is the equivalent of using the field name as the
index into the collection.
--
Regards,
Jay Freedman
Microsoft Word MVP FAQ:
http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.- Hide quoted text -
- Show quoted text -
Thanks for the suggestion. Unfortunately no improvements.
Any ability to compile/semi-compile VBA code in Word?
As I understand it it is lots faster to read Access from within Word
using VBA and DAO than it would be to use OLE from Access - right?
Would ADO be significantly faster?
This really sucks. So far, after having coded the loads of 7 1/2 or
26 pages of the document, the load takes 38 seconds. by extrapolation
we are looking at 3 minutes to load.
Here is the Word document I am trying to interface with. The clients
wants all text boxes to be variable in size - number of characters -
just like they can easily be in Word. IN Word everything shifts down
when this happens.
http://advantage.ok.gov/doc.aspx?id=297
and here is the first few lines of my code for anyone to critique:
Sub LoadData()
Call LoadData1
Call LoadData2
End Sub
Sub LoadData1()
' LoadData2 Macro
' Macro created 6/16/2009 by Bob Alston
'***** Load data into an open Word document.............
Dim lresponse As Integer
Dim db As DAO.Database
Dim inrec As DAO.Recordset
Dim cnt As Long
Dim selstr As String
Dim TheTable As String
Dim keyuca As Long
Word.Application.Visible = False
keyuca = -1461653180
'*** Process tbl_UCA
TheTable = "tbl_UCA"
'**** define the database object
Set db = DBEngine.OpenDatabase("c:\testData.mdb")
'***** Set error handling
On Error Resume Next
'***** Open up the input table
Set inrec = db.OpenRecordset(TheTable)
'**** select the current record to be copied
selstr = "[keyuca] = " & keyuca
inrec.FindFirst selstr
'***** Write fields to Word document
With ActiveDocument.FormFields
If inrec.Fields("1Assessment") Then .Item("assessment").CheckBox.Value
= True
If inrec.Fields("1ReAssessment") Then .Item
("Reassessment").CheckBox.Value = True
If inrec.Fields("1LocAccessHome") Then .Item
("LocAccessHome").CheckBox.Value = True
If inrec.Fields("1LocAccessRelative") Then .Item
("LocAccessRelative").CheckBox.Value = True
If inrec.Fields("1LocAccessNH") Then .Item
("LocAccessNH").CheckBox.Value = True
If inrec.Fields("1LocAccessHospital") Then .Item
("LocAccessHospital").CheckBox.Value = True
If inrec.Fields("1LocAccessother") Then
.Item("LocAccessother").CheckBox.Value = True
.Item("locAccessOtherDescr").Result = inrec.Fields
("1locAccessOtherDescr")
End If
.Item("mentaltexta").Result = inrec.Fields("1mentaltexta")
.Item("mentalscorea").Result = inrec.Fields("1mentalscorea")
.Item("assessment1").Result = inrec.Fields("1mentalscoreaweighted")
.Item("mentaltextb").Result = inrec.Fields("1mentaltextb")
.Item("mentalscoreb").Result = inrec.Fields("1mentalscoreb")
.Item("assessment2").Result = inrec.Fields("1mentalscorebweighted")
.Item("mentaltextc").Result = inrec.Fields("1mentaltextc")
.Item("mentaltextc2").Result = inrec.Fields("1mentaltextc2")
.Item("mentalscorec").Result = inrec.Fields("1mentalscorec")
.Item("assessment3").Result = inrec.Fields("1mentalscorecweighted")
.Item("mentalscored").Result = inrec.Fields("1mentalscored")
.Item("assessment4").Result = inrec.Fields("1mentalscoredweighted")
.Item("mentaltextd1").Result = inrec.Fields("1mentaltextd1")
.Item("mentaltextd2").Result = inrec.Fields("1mentaltextd2")
.Item("mentaltextd3").Result = inrec.Fields("1mentaltextd3")
.Item("mentaltextd4").Result = inrec.Fields("1mentaltextd4")
.Item("mentaltextd5").Result = inrec.Fields("1mentaltextd5")
.Item("mentaltextd6").Result = inrec.Fields("1mentaltextd6")
.Item("mentaltextd7").Result = inrec.Fields("1mentaltextd7")
.Item("mentaltextd8").Result = inrec.Fields("1mentaltextd8")
.Item("mentaltextd9").Result = inrec.Fields("1mentaltextd9")
.Item("mentaltextd10").Result = inrec.Fields("1mentaltextd10")
.Item("mentaltextd11").Result = inrec.Fields("1mentaltextd11")
.Item("mentaltextd12").Result = inrec.Fields("1mentaltextd12")
.Item("mentaltextd13").Result = inrec.Fields("1mentaltextd13")
.Item("mentaltextd14").Result = inrec.Fields("1mentaltextd14")
.Item("mentaltextd15").Result = inrec.Fields("1mentaltextd15")
.Item("mentaltextd16").Result = inrec.Fields("1mentaltextd16")
.Item("mentaltextd17").Result = inrec.Fields("1mentaltextd17")
.Item("mentaltextd18").Result = inrec.Fields("1mentaltextd18")
.Item("mentaltextd19").Result = inrec.Fields("1mentaltextd19")
.Item("mentaltextd20").Result = inrec.Fields("1mentaltextd20")- Hide quoted text -
- Show quoted text -
