Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    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?

    Thanks a ton

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Database Performance

    Can you post the procedure to see if there can be any optimization done?
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Here is the procedure that is listed in the on click property. Hopefully this will help.

    Option Compare Database
    Option Explicit

    Private Const conClearMac As String = "mac qclr re lease"
    Private Const conTemplate As String = "re lease.doc"



    Private Sub Form_Open(Cancel As Integer)
    DoCmd.RunMacro (conClearMac)
    End Sub


    Private Sub cmdGo_Click()

    ' Must Close Query before Mail Merge, else Data is N/A.
    DoCmd.Close
    Call MailMerge

    ' After Mail Merge, clear Query
    DoCmd.RunMacro (conClearMac)


    End Sub



    Private Sub Close_Form_Click()
    On Error GoTo Err_Close_Form_Click

    DoCmd.RunMacro (conClearMac)

    DoCmd.Close

    DoCmd.RunMacro (conClearMac)

    Exit_Close_Form_Click:
    Exit Sub

    Err_Close_Form_Click:
    MsgBox Err.Description
    Resume Exit_Close_Form_Click

    End Sub
    Public Sub MailMerge()
    Dim strPath As String
    Dim strDataSource As String

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

    On Error GoTo HandleErrors

    strPath = FixPath(CurrentProject.Path)


    ' Start Word using mailmerge template
    Set wrdApp = New Word.Application
    Set doc = wrdApp.Documents.Add(strPath & conTemplate)

    ' 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

    ' Display the mail merge document
    wrdApp.Visible = True

    ExitHere:

    Set doc = Nothing
    Set wrdApp = Nothing
    Exit Sub


    HandleErrors:
    Select Case Err.Number
    Case 53
    Resume Next
    Case Else
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
    End Select
    Resume

    Set doc = Nothing
    Set wrdApp = Nothing

    End Sub

    Private Function FixPath(strPath As String) As String
    If Right(strPath, 1) = "\" Then
    FixPath = strPath
    Else
    FixPath = strPath & "\"
    End If
    End Function

    Private Sub LEASE_NO_AfterUpdate()

    End Sub

    Private Sub LEASE_NO_BeforeUpdate(Cancel As Integer)

    End Sub

    Private Sub Rema_Name_BeforeUpdate(Cancel As Integer)

    End Sub

    Private Sub Text113_BeforeUpdate(Cancel As Integer)

    End Sub

    Private Sub Text115_BeforeUpdate(Cancel As Integer)

    End Sub

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    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.

    Thanks

  5. #5
    Join Date
    Apr 2004
    Location
    Pune, India
    Posts
    14
    Hello Friend,

    Just try the below mentioned option and see if performance increses.

    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. !

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    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..

  7. #7
    Join Date
    Mar 2004
    Posts
    361
    Thanks a ton. I have a user on the program right now but I'll change it over lunch and let you guys know what happened. Again thanks.

    I love this forum!

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    yea i practically get all the answers to my questins here in this forum..

  9. #9
    Join Date
    Mar 2004
    Posts
    361
    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.

    Thanks

  10. #10
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    well, you would change the "Dim objWord" part to your name, which is wrdapp i think...but everything else if fine

  11. #11
    Join Date
    Mar 2004
    Posts
    361
    I tried to add that code and it didn't help the speed of the merge. The merge worked but the it still took like 8-15 sec. Do you have any other ideas??

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    well the merge would be faster on the second run...or if u already have word open....if it's still slow, tell me

  13. #13
    Join Date
    Mar 2004
    Posts
    361
    I opened a blank document in word and left it open while I ran the merge. I ran it 5 times and it is still way slow.

  14. #14
    Join Date
    Mar 2004
    Posts
    361
    I actually entered the change in a different form but they pretty much use the same code. Would that make a difference?

  15. #15
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    depends on how different the code is...but it should be the same..

    try putting the macro in word and calling the macro from access...according to my sources, its significantly faster...


    or try outputting the data source as an RTF file and then using that as the data source for the mail merge...its also faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •