If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Database Performance

I have a form that queries information from a table. The user is allowed to add information to specific textboxes after the query is completed on the form. At this point the user presses a button and merges the total data with word. My problem lies in the fact that once the user presses the button to merge with word, the time it takes to merge is lengthy. I can be as fast as 5 seconds but as long a 20. Is there any way that I can improve the performance of this procedure?

' Do the mail merge to a new document.
With doc.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
If .State = wdMainAndDataSource Then .Execute
End With

I know that seems like a lot of stuff to go through but I has to be in this code. I don't have the skills to go through line by line but I would be greatful if someone could take a look at it and give me an idea on what to change.

1)Include the reference of Rich Text Box
2) Not enter the control on the form, make Visible=False. Now throw the output to text file and load that text file in the rich text box and save it as RTF. This is much much faster, and more of all, is consistent. (Won't give you the range of 5-20 !) Needless to say that you need not load the word object also. !

Instead of always initializing...
Dim wrdapp as new word.application..

that always forces access to startup a new word app. This is lengthy since after about 5 button clicks, you can look in the task manager and boy, you got 5 WINWORD.exe apps running and hogging computer space.

Try this:

Dim objWord As Word.Application
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err().number <> 0 Then
Err.Clear
Set objWord = CreateObject("Word.Application")
End If

This way, the first button click either starts word if its not there, or used the word in the background if its already there...this saves some time..

Just to make sure that I am putting this in the right place let me show you what i'm going to do.

Section of my old code:

Dim doc As Word.Document
Dim wrdApp As Word.Application

On Error GoTo HandleErrors

strPath = FixPath(CurrentProject.Path)

What new code should look like:

Dim doc As Word.Document
Dim objWord As Word.Application
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err().number <> 0 Then
Err.Clear
Set objWord = CreateObject("Word.Application")
End If

On Error GoTo HandleErrors

strPath = FixPath(CurrentProject.Path)

I hope that is right. Also, for aqua_zinc I don't really know how to do what you are talking about. You might have to walk be through that idea. I hope that is ok.