Results 1 to 6 of 6
  1. #1
    David92595 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    22

    how to speed up a search on a view

    Hello,



    I have a few (currently unlucky) remote workers that are trying to mail merge word documents. They are connected to our domain; they have the ODBC connection set up to our SQL server 2008. They have access to the documents on our network, but when they mail merge a document it usually times out. I say usually, because did some testing and it takes about 30 seconds for the mail merge to gather the mail merge information for record 200, we have close to 16,000 records...

    The mail merge is based off a view in our SQL database. I have tried recreating the mail merge with Schema binding so I could create a unique Clustered Index on the base table's primary key. Which is also the field we use to search for our records. But it has not hepled the reponse time of the mail merge.

    This they anything I can do to speed things up? Our remote workers are now at a dead stop and cannot continue working!.

    Thank you,

    David92595

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I'd have to have a more complete description of your methods to have any practical advice. I just sped up one export process by a factor of 6 simply by declaring some reference variables. Things like
    Code:
       Set myApp = myBookMgd.Application
       With myApp
          .Calculation = xlCalculationManual
          .ScreenUpdating = False
          .EnableEvents = False
       End With
    instead of
    Code:
      
       myBookMgd.Application.Calculation = xlCalculationManual
       myBookMgd.Application.ScreenUpdating = False
       myBookMgd.Application.EnableEvents = False
    The reason the first version is more efficient is because VBA has to resolve each object (myBookMgd, then .Application) each time in the second version, but only once in the first. (Note: the reference variable myApp is used again a few lines down.)


    Just a thought, but I'm wondering why the work is being done at the server end for your mail merge. If the data required to be merged were pushed to a table in the front end, and the doc were likewise pushed to the frontend PC, then the only limitation is the speed of the PC itself.

    The second question is, where's the mail being sent out from?

    So, my question back is, exactly what applications are talking to each other to make this process happen? Is Access pulling informatoin from SQL server and executing Word to create a mailmerge that goes to Outlook, or what?

  3. #3
    David92595 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    22
    okay, I'll do my best here, this is my first issue with the speed of a mail merge.

    As a correction, the ODBC connection is for use with our access front end. It has nothing to do with our mail merge.

    Currently, we have data sources set up that connect specific views on our SQL Server. When a word document is opened a pop-up window appears stating "Opening this document will run the follow SQL command: SELECT * FROM "SQL Mail Merge" Data from your database will be placed in the document. Do you want to continue?"
    ("SQL Mail Merge" is the view set up specifically for our mail merging). After the user clicks yes the documents opens, and the user goes to the mailings tab --> Find Recipient --> enters the ID number they are looking for and selects "ID" in the "This field:" drop down list.

    For our onsite workers everything works GREAT, they have the information they need in a matter of seconds. Our offsite users are essentially timming out or canceling out after 5+ minutes of waiting.

    As previously stated I have recently created a unique clusted index on the ID column, because thats the column we use to search for from the work document.

  4. #4
    David92595 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    22
    One more update,

    I am trying to mail merge over an SSL VPN connection. Not sure if that will help anyone at all, but I wanted to get that out there.

    After sitting on this for a day or two, is there a macro I can run that will pull the specific data I need from the data source connection? If I were able to enter in the Primary Key of the table?

  5. #5
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, so how much data is returned by SELECT * FROM "SQL Mail Merge"? That SQL looks like it is returning all the fields in that View. How many fields is that, times how many records?

    And are your remote users timing out after they've chosen the ID, or before?

    Presumably, you'd be better off breaking the process into two phases: first, loading only the information your users need to choose the ID,
    Code:
    SELECT ID, ContactName FROM "SQL Mail Merge";
    And then second, your actual mailmerge, which should be something like
    Code:
    SELECT * FROM "SQL Mail Merge"
    WHERE ID = [variable containing the selected value];

  6. #6
    David92595 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    22
    Quote Originally Posted by Dal Jeanis View Post
    Okay, so how much data is returned by SELECT * FROM "SQL Mail Merge"? That SQL looks like it is returning all the fields in that View. How many fields is that, times how many records?

    And are your remote users timing out after they've chosen the ID, or before?

    Presumably, you'd be better off breaking the process into two phases: first, loading only the information your users need to choose the ID,
    Code:
    SELECT ID, ContactName FROM "SQL Mail Merge";
    And then second, your actual mailmerge, which should be something like
    Code:
    SELECT * FROM "SQL Mail Merge"
    WHERE ID = [variable containing the selected value];

    That sounds like exactly what I need to do! So How do I implement it? My users are starting in a word doc and using the "Find Recipient" button under Mailings. Am I going to have to create a form for the user to enter in the ID in word along with a macro to run this code?

    David9295

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 08-05-2013, 06:23 AM
  2. Replies: 1
    Last Post: 07-21-2011, 01:57 PM
  3. Increase search speed
    By kjuelfs in forum Queries
    Replies: 3
    Last Post: 07-12-2010, 05:11 AM
  4. Replies: 4
    Last Post: 01-11-2010, 11:41 PM
  5. search,view and open function
    By blurboy84 in forum Reports
    Replies: 0
    Last Post: 10-08-2009, 11:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums