Action Query Slow and Hourglass Method
in [Queries]
|
Prev: The best way to set up a payment schedule...PLEASE HELP!!!
Next: Finding the space in a text string and returning text on eithersi
From: Kay on 12 Mar 2010 12:31 Two questions if you please. When my database opens it runs an action query that deletes the records in a temp table that is used as the record source for a phone indiex form and then reloads the newest 300,000+ records. This technique really helped to speed up the form with the phone index however, the end-user has to wait for at least 20 seconds for the startup form to load. Is this reasonable or is there a way to speed up the process? The source table does have indexes set on the fields that are being loaded in the temp table. I have things like autoname correct deactivated in Options. Second: The startup form uses the state seal image in place of the command button to open the entry form for that state. I would like the hourglass to show once they click. I added code like this but does not work. Private Sub Image22_Click() 'DoCmd.Close Application.Screen.MousePointer = 11 ' Hourglass ' Do some background activity. DoCmd.OpenForm "frmExpenseEntryforOrgs", acNormal Application.Screen.MousePointer = 0 ' Back to normal End Sub would appreciate your help!
From: Jerry Whittle on 12 Mar 2010 12:57 Does the temp table have indexes or a primary key field? If so these would slow down both the delete and append queries. However I think that 20 seconds is reasonable for that action on 300,000+ records when using an Access database. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kay" wrote: > Two questions if you please. > > When my database opens it runs an action query that deletes the records in a > temp table that is used as the record source for a phone indiex form and then > reloads the newest 300,000+ records. This technique really helped to speed > up the form with the phone index however, the end-user has to wait for at > least 20 seconds for the startup form to load. Is this reasonable or is > there a way to speed up the process? The source table does have indexes set > on the fields that are being loaded in the temp table. I have things like > autoname correct deactivated in Options. > > Second: The startup form uses the state seal image in place of the command > button to open the entry form for that state. I would like the hourglass to > show once they click. I added code like this but does not work. > Private Sub Image22_Click() > 'DoCmd.Close > Application.Screen.MousePointer = 11 ' Hourglass > ' Do some background activity. > DoCmd.OpenForm "frmExpenseEntryforOrgs", acNormal > > Application.Screen.MousePointer = 0 ' Back to normal > > > > End Sub > > would appreciate your help!
From: John Spencer on 12 Mar 2010 13:09 20 seconds is pretty good to add 300,000 records. So I don't think there is much you can do to speed this up. Try using DoCmd.HourGlass True and DoCmd.HourGlass False Although what you have should be working. You might try adding DoEvents right after you turn on the hour glass cursor. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Kay wrote: > Two questions if you please. > > When my database opens it runs an action query that deletes the records in a > temp table that is used as the record source for a phone indiex form and then > reloads the newest 300,000+ records. This technique really helped to speed > up the form with the phone index however, the end-user has to wait for at > least 20 seconds for the startup form to load. Is this reasonable or is > there a way to speed up the process? The source table does have indexes set > on the fields that are being loaded in the temp table. I have things like > autoname correct deactivated in Options. > > Second: The startup form uses the state seal image in place of the command > button to open the entry form for that state. I would like the hourglass to > show once they click. I added code like this but does not work. > Private Sub Image22_Click() > 'DoCmd.Close > Application.Screen.MousePointer = 11 ' Hourglass > ' Do some background activity. > DoCmd.OpenForm "frmExpenseEntryforOrgs", acNormal > > Application.Screen.MousePointer = 0 ' Back to normal > > > > End Sub > > would appreciate your help!
From: Kay on 12 Mar 2010 13:36 Jerry, Thanks for replying. The answer is no to the temp table having a primary key and index, but if you think that amount of time is reasonable, I am okay with it. The other question that relates to not actually using a command button but a graphic could be solved I guess by using a hyperlink instead of an event to open the form. Nothing else occurs in the event when you open the form anyways. I was just curious about why the code would not work against the object. It is nice to use the traditional pointer to select the button, but the hourglass to show the form in the process of opening. This particular form is the one that uses the 300,000 records in the phone index form so it takes a few seconds to open. The other forms open in a split second. Thanks! "Jerry Whittle" wrote: > Does the temp table have indexes or a primary key field? If so these would > slow down both the delete and append queries. > > However I think that 20 seconds is reasonable for that action on 300,000+ > records when using an Access database. > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "Kay" wrote: > > > Two questions if you please. > > > > When my database opens it runs an action query that deletes the records in a > > temp table that is used as the record source for a phone indiex form and then > > reloads the newest 300,000+ records. This technique really helped to speed > > up the form with the phone index however, the end-user has to wait for at > > least 20 seconds for the startup form to load. Is this reasonable or is > > there a way to speed up the process? The source table does have indexes set > > on the fields that are being loaded in the temp table. I have things like > > autoname correct deactivated in Options. > > > > Second: The startup form uses the state seal image in place of the command > > button to open the entry form for that state. I would like the hourglass to > > show once they click. I added code like this but does not work. > > Private Sub Image22_Click() > > 'DoCmd.Close > > Application.Screen.MousePointer = 11 ' Hourglass > > ' Do some background activity. > > DoCmd.OpenForm "frmExpenseEntryforOrgs", acNormal > > > > Application.Screen.MousePointer = 0 ' Back to normal > > > > > > > > End Sub > > > > would appreciate your help!
From: Kay on 12 Mar 2010 14:51
John, Yes I tried that first. It must not execute on click of an image. "John Spencer" wrote: > 20 seconds is pretty good to add 300,000 records. So I don't think there is > much you can do to speed this up. > > Try using > DoCmd.HourGlass True > and > DoCmd.HourGlass False > > Although what you have should be working. You might try adding > DoEvents > right after you turn on the hour glass cursor. > > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Kay wrote: > > Two questions if you please. > > > > When my database opens it runs an action query that deletes the records in a > > temp table that is used as the record source for a phone indiex form and then > > reloads the newest 300,000+ records. This technique really helped to speed > > up the form with the phone index however, the end-user has to wait for at > > least 20 seconds for the startup form to load. Is this reasonable or is > > there a way to speed up the process? The source table does have indexes set > > on the fields that are being loaded in the temp table. I have things like > > autoname correct deactivated in Options. > > > > Second: The startup form uses the state seal image in place of the command > > button to open the entry form for that state. I would like the hourglass to > > show once they click. I added code like this but does not work. > > Private Sub Image22_Click() > > 'DoCmd.Close > > Application.Screen.MousePointer = 11 ' Hourglass > > ' Do some background activity. > > DoCmd.OpenForm "frmExpenseEntryforOrgs", acNormal > > > > Application.Screen.MousePointer = 0 ' Back to normal > > > > > > > > End Sub > > > > would appreciate your help! > . > |