How can I time long running queries?

Hello all,

Is it possible to time long running select queries?

I was looking at creating some code to do this....something like:

1.  Store current time in a time variable
2.  Try to programmatically open a query ("open" meaning just like manually 
opening a select query from MS Access to view data) using 
"Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
3.  Check the current time and compare it to the start time saved at the 
beginning

But, I get "Run-time error '3219': Invalid operation." when I run 
"...OpenRecordset dbOp...".  Also, I get the feeling that the OpenRecordset 
method is for opening a query in memory/the background (storing in a 
Recordset object variable, not for viewing) rather than actually opening a 
query to view the data).

Thanks for any help anyone can provide,

Conan Kelly 


0
Conan
1/17/2008 12:02:23 AM
access.queries 6343 articles. 1 followers. Follow

9 Replies
801 Views

Similar Articles

[PageSpeed] 53

Conan

Would you describe what having the query data will allow you to accomplish? 
You've described a "how" (open a query, view the results), but not the 
"why".  It may be that there are other ways to get done what you're after.

NOTE:  "long running queries" may run long for several reasons, among them 
the possibility that the join, sort and/or selection fields are not indexed 
properly, and the possibility that data from non-local sources (e.g., linked 
SQL-Server table) is being subjected to functions that ONLY exist in the 
local (Access) application.  Do any of these apply in your situation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
>
> Is it possible to time long running select queries?
>
> I was looking at creating some code to do this....something like:
>
> 1.  Store current time in a time variable
> 2.  Try to programmatically open a query ("open" meaning just like 
> manually opening a select query from MS Access to view data) using 
> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
> 3.  Check the current time and compare it to the start time saved at the 
> beginning
>
> But, I get "Run-time error '3219': Invalid operation." when I run 
> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
> OpenRecordset method is for opening a query in memory/the background 
> (storing in a Recordset object variable, not for viewing) rather than 
> actually opening a query to view the data).
>
> Thanks for any help anyone can provide,
>
> Conan Kelly
> 


0
Jeff
1/17/2008 12:10:40 AM
Jeff,

Thanks for your feedback.

All of the data is inside this AC DB (no linked tables SQL Server or 
external text/XL files).  Normally we set up a query to match our XL 
templates, open the query, copy-n-paste from AC to XL.

I'd like to keep that manual copy-n-paste procedure (don't want to 
automate/program it) so I can see the data.

The reason it is running so slow is because this client sends us this DB. 
It is not a.....I forget the term....."standardized" DB:  there are 12 
tables with the exact same table definitions (number of columns, column 
names, data types).  I don't want to put all the data in one table because I 
want to leave the clients data unchanged and I don't want to duplicate the 
data (taking up more disk space)......so I have these 12 tables UNION ALL'ed 
together in a query (as well as 0's converted to nulls using Iif() in the 
last 37 of 44 columns).  The recordset of this UNION ALL query is 1.58 mil 
records.

Then I use the 1.58-mil-records UNION ALL query in another query to look up 
an order # and category (separate lookup tables).

Then I use this "Order & Cat" query in a group by select query to summarize 
data into 14 categories/order #'s, summing or counting the last 37 of 44 
columns, turning 1.58 mil rows into 57.

It is from this 57-row summarized query that I copy-n-paste into XL.

I'd like to keep this process the same, I was just curious as to how long it 
was taking for these queries to run and I didn't want to sit here and 
twiddle my thumbs, watching my watch, waiting for it finish.  It is maybe 
something like 5-10 mins.

Thanks again for all of your help,

Conan






"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:uYxAm1JWIHA.5208@TK2MSFTNGP04.phx.gbl...
> Conan
>
> Would you describe what having the query data will allow you to 
> accomplish? You've described a "how" (open a query, view the results), but 
> not the "why".  It may be that there are other ways to get done what 
> you're after.
>
> NOTE:  "long running queries" may run long for several reasons, among them 
> the possibility that the join, sort and/or selection fields are not 
> indexed properly, and the possibility that data from non-local sources 
> (e.g., linked SQL-Server table) is being subjected to functions that ONLY 
> exist in the local (Access) application.  Do any of these apply in your 
> situation?
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
> news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
>> Hello all,
>>
>> Is it possible to time long running select queries?
>>
>> I was looking at creating some code to do this....something like:
>>
>> 1.  Store current time in a time variable
>> 2.  Try to programmatically open a query ("open" meaning just like 
>> manually opening a select query from MS Access to view data) using 
>> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
>> 3.  Check the current time and compare it to the start time saved at the 
>> beginning
>>
>> But, I get "Run-time error '3219': Invalid operation." when I run 
>> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
>> OpenRecordset method is for opening a query in memory/the background 
>> (storing in a Recordset object variable, not for viewing) rather than 
>> actually opening a query to view the data).
>>
>> Thanks for any help anyone can provide,
>>
>> Conan Kelly
>>
>
> 


0
Conan
1/17/2008 12:56:02 AM
You didn't mention whether there are any indexes.  It might be worth a 
look...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
news:CIxjj.442610$kj1.100173@bgtnsc04-news.ops.worldnet.att.net...
> Jeff,
>
> Thanks for your feedback.
>
> All of the data is inside this AC DB (no linked tables SQL Server or 
> external text/XL files).  Normally we set up a query to match our XL 
> templates, open the query, copy-n-paste from AC to XL.
>
> I'd like to keep that manual copy-n-paste procedure (don't want to 
> automate/program it) so I can see the data.
>
> The reason it is running so slow is because this client sends us this DB. 
> It is not a.....I forget the term....."standardized" DB:  there are 12 
> tables with the exact same table definitions (number of columns, column 
> names, data types).  I don't want to put all the data in one table because 
> I want to leave the clients data unchanged and I don't want to duplicate 
> the data (taking up more disk space)......so I have these 12 tables UNION 
> ALL'ed together in a query (as well as 0's converted to nulls using Iif() 
> in the last 37 of 44 columns).  The recordset of this UNION ALL query is 
> 1.58 mil records.
>
> Then I use the 1.58-mil-records UNION ALL query in another query to look 
> up an order # and category (separate lookup tables).
>
> Then I use this "Order & Cat" query in a group by select query to 
> summarize data into 14 categories/order #'s, summing or counting the last 
> 37 of 44 columns, turning 1.58 mil rows into 57.
>
> It is from this 57-row summarized query that I copy-n-paste into XL.
>
> I'd like to keep this process the same, I was just curious as to how long 
> it was taking for these queries to run and I didn't want to sit here and 
> twiddle my thumbs, watching my watch, waiting for it finish.  It is maybe 
> something like 5-10 mins.
>
> Thanks again for all of your help,
>
> Conan
>
>
>
>
>
>
> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
> news:uYxAm1JWIHA.5208@TK2MSFTNGP04.phx.gbl...
>> Conan
>>
>> Would you describe what having the query data will allow you to 
>> accomplish? You've described a "how" (open a query, view the results), 
>> but not the "why".  It may be that there are other ways to get done what 
>> you're after.
>>
>> NOTE:  "long running queries" may run long for several reasons, among 
>> them the possibility that the join, sort and/or selection fields are not 
>> indexed properly, and the possibility that data from non-local sources 
>> (e.g., linked SQL-Server table) is being subjected to functions that ONLY 
>> exist in the local (Access) application.  Do any of these apply in your 
>> situation?
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>> news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
>>> Hello all,
>>>
>>> Is it possible to time long running select queries?
>>>
>>> I was looking at creating some code to do this....something like:
>>>
>>> 1.  Store current time in a time variable
>>> 2.  Try to programmatically open a query ("open" meaning just like 
>>> manually opening a select query from MS Access to view data) using 
>>> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
>>> 3.  Check the current time and compare it to the start time saved at the 
>>> beginning
>>>
>>> But, I get "Run-time error '3219': Invalid operation." when I run 
>>> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
>>> OpenRecordset method is for opening a query in memory/the background 
>>> (storing in a Recordset object variable, not for viewing) rather than 
>>> actually opening a query to view the data).
>>>
>>> Thanks for any help anyone can provide,
>>>
>>> Conan Kelly
>>>
>>
>>
>
> 


0
Jeff
1/17/2008 3:35:08 PM
sorry...there are no indexes.  Also, I don't quite understand how to use 
them/why they speed things up...so I don't really use them too much (in both 
AC & SQL Server).

But we haven't had much of a need for them either.  Most of our DB's are 
just snapshots of clients data.  The are not live DB's that have many users 
in them looking up/changing data.  Usually there is one person in them 
(usually me) that just runs queries and copies results to XL files.  And 
they get bulk inserts as often as every 3 months.  Everything we have runs 
pretty quickly, so I'm not too concerned with speed.  There are just a 
handfull that take longer than 5 mins to run, and if they take much longer, 
I find ways to speed them up (i.e. make temp tables out of query/view 
results, then drop them when I'm done.)

Thanks again,

Conan




"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:uVBuL6RWIHA.4448@TK2MSFTNGP03.phx.gbl...
> You didn't mention whether there are any indexes.  It might be worth a 
> look...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
> news:CIxjj.442610$kj1.100173@bgtnsc04-news.ops.worldnet.att.net...
>> Jeff,
>>
>> Thanks for your feedback.
>>
>> All of the data is inside this AC DB (no linked tables SQL Server or 
>> external text/XL files).  Normally we set up a query to match our XL 
>> templates, open the query, copy-n-paste from AC to XL.
>>
>> I'd like to keep that manual copy-n-paste procedure (don't want to 
>> automate/program it) so I can see the data.
>>
>> The reason it is running so slow is because this client sends us this DB. 
>> It is not a.....I forget the term....."standardized" DB:  there are 12 
>> tables with the exact same table definitions (number of columns, column 
>> names, data types).  I don't want to put all the data in one table 
>> because I want to leave the clients data unchanged and I don't want to 
>> duplicate the data (taking up more disk space)......so I have these 12 
>> tables UNION ALL'ed together in a query (as well as 0's converted to 
>> nulls using Iif() in the last 37 of 44 columns).  The recordset of this 
>> UNION ALL query is 1.58 mil records.
>>
>> Then I use the 1.58-mil-records UNION ALL query in another query to look 
>> up an order # and category (separate lookup tables).
>>
>> Then I use this "Order & Cat" query in a group by select query to 
>> summarize data into 14 categories/order #'s, summing or counting the last 
>> 37 of 44 columns, turning 1.58 mil rows into 57.
>>
>> It is from this 57-row summarized query that I copy-n-paste into XL.
>>
>> I'd like to keep this process the same, I was just curious as to how long 
>> it was taking for these queries to run and I didn't want to sit here and 
>> twiddle my thumbs, watching my watch, waiting for it finish.  It is maybe 
>> something like 5-10 mins.
>>
>> Thanks again for all of your help,
>>
>> Conan
>>
>>
>>
>>
>>
>>
>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>> news:uYxAm1JWIHA.5208@TK2MSFTNGP04.phx.gbl...
>>> Conan
>>>
>>> Would you describe what having the query data will allow you to 
>>> accomplish? You've described a "how" (open a query, view the results), 
>>> but not the "why".  It may be that there are other ways to get done what 
>>> you're after.
>>>
>>> NOTE:  "long running queries" may run long for several reasons, among 
>>> them the possibility that the join, sort and/or selection fields are not 
>>> indexed properly, and the possibility that data from non-local sources 
>>> (e.g., linked SQL-Server table) is being subjected to functions that 
>>> ONLY exist in the local (Access) application.  Do any of these apply in 
>>> your situation?
>>>
>>> Good luck!
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>>> news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
>>>> Hello all,
>>>>
>>>> Is it possible to time long running select queries?
>>>>
>>>> I was looking at creating some code to do this....something like:
>>>>
>>>> 1.  Store current time in a time variable
>>>> 2.  Try to programmatically open a query ("open" meaning just like 
>>>> manually opening a select query from MS Access to view data) using 
>>>> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
>>>> 3.  Check the current time and compare it to the start time saved at 
>>>> the beginning
>>>>
>>>> But, I get "Run-time error '3219': Invalid operation." when I run 
>>>> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
>>>> OpenRecordset method is for opening a query in memory/the background 
>>>> (storing in a Recordset object variable, not for viewing) rather than 
>>>> actually opening a query to view the data).
>>>>
>>>> Thanks for any help anyone can provide,
>>>>
>>>> Conan Kelly
>>>>
>>>
>>>
>>
>>
>
> 


0
Conan
1/17/2008 4:41:33 PM
Hi -

Not sure where I got this or I'd give the author credit.  It works!  

Option Compare Database
Option Explicit

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
    lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
    a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function


Function QueryTimer(strQueryName As String)

Dim db  As Database
Dim qry As QueryDef
Dim rs  As Recordset

Set db = CurrentDb
Set qry = db.QueryDefs(strQueryName)

'Start the clock
a2kuStartClock
Set rs = qry.OpenRecordset()

'Stop the clock and print the results to the debug window
Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
" milliseconds"

rs.Close
db.Close
Set db = Nothing

End Function


Conan Kelly wrote:
>sorry...there are no indexes.  Also, I don't quite understand how to use 
>them/why they speed things up...so I don't really use them too much (in both 
>AC & SQL Server).
>
>But we haven't had much of a need for them either.  Most of our DB's are 
>just snapshots of clients data.  The are not live DB's that have many users 
>in them looking up/changing data.  Usually there is one person in them 
>(usually me) that just runs queries and copies results to XL files.  And 
>they get bulk inserts as often as every 3 months.  Everything we have runs 
>pretty quickly, so I'm not too concerned with speed.  There are just a 
>handfull that take longer than 5 mins to run, and if they take much longer, 
>I find ways to speed them up (i.e. make temp tables out of query/view 
>results, then drop them when I'm done.)
>
>Thanks again,
>
>Conan
>
>> You didn't mention whether there are any indexes.  It might be worth a 
>> look...
>[quoted text clipped - 86 lines]
>>>>>
>>>>> Conan Kelly

-- 
Message posted via http://www.accessmonster.com

0
raskew
1/17/2008 5:04:14 PM
raskew,

Thanks for the code.  That will do exactly what I want.

Thanks again,

Conan




"raskew via AccessMonster.com" <u28575@uwe> wrote in message 
news:7e5a52e003d92@uwe...
> Hi -
>
> Not sure where I got this or I'd give the author credit.  It works!
>
> Option Compare Database
> Option Explicit
>
> Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
> Alias "timeGetTime" () As Long
> Dim lngstartingtime As Long
>
> Sub a2kuStartClock()
>    lngstartingtime = a2Ku_apigettime()
> End Sub
> Function a2kuEndClock()
>    a2kuEndClock = a2Ku_apigettime() - lngstartingtime
> End Function
>
>
> Function QueryTimer(strQueryName As String)
>
> Dim db  As Database
> Dim qry As QueryDef
> Dim rs  As Recordset
>
> Set db = CurrentDb
> Set qry = db.QueryDefs(strQueryName)
>
> 'Start the clock
> a2kuStartClock
> Set rs = qry.OpenRecordset()
>
> 'Stop the clock and print the results to the debug window
> Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
> " milliseconds"
>
> rs.Close
> db.Close
> Set db = Nothing
>
> End Function
>
>
> Conan Kelly wrote:
>>sorry...there are no indexes.  Also, I don't quite understand how to use
>>them/why they speed things up...so I don't really use them too much (in 
>>both
>>AC & SQL Server).
>>
>>But we haven't had much of a need for them either.  Most of our DB's are
>>just snapshots of clients data.  The are not live DB's that have many 
>>users
>>in them looking up/changing data.  Usually there is one person in them
>>(usually me) that just runs queries and copies results to XL files.  And
>>they get bulk inserts as often as every 3 months.  Everything we have runs
>>pretty quickly, so I'm not too concerned with speed.  There are just a
>>handfull that take longer than 5 mins to run, and if they take much 
>>longer,
>>I find ways to speed them up (i.e. make temp tables out of query/view
>>results, then drop them when I'm done.)
>>
>>Thanks again,
>>
>>Conan
>>
>>> You didn't mention whether there are any indexes.  It might be worth a
>>> look...
>>[quoted text clipped - 86 lines]
>>>>>>
>>>>>> Conan Kelly
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Conan
1/17/2008 7:17:21 PM
Conan

Usually, a field that gets used for joining, for sorting, and/or for 
selecting on can be indexed for faster query performance.  Of course there 
are exceptions, and indexing is part art, part science... you usually have 
to just try to see if it works better.


Regards

Jeff Boyce
Microsoft Office/Access MVP

"Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
news:1zLjj.142802$MJ6.7279@bgtnsc05-news.ops.worldnet.att.net...
> sorry...there are no indexes.  Also, I don't quite understand how to use 
> them/why they speed things up...so I don't really use them too much (in 
> both AC & SQL Server).
>
> But we haven't had much of a need for them either.  Most of our DB's are 
> just snapshots of clients data.  The are not live DB's that have many 
> users in them looking up/changing data.  Usually there is one person in 
> them (usually me) that just runs queries and copies results to XL files. 
> And they get bulk inserts as often as every 3 months.  Everything we have 
> runs pretty quickly, so I'm not too concerned with speed.  There are just 
> a handfull that take longer than 5 mins to run, and if they take much 
> longer, I find ways to speed them up (i.e. make temp tables out of 
> query/view results, then drop them when I'm done.)
>
> Thanks again,
>
> Conan
>
>
>
>
> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
> news:uVBuL6RWIHA.4448@TK2MSFTNGP03.phx.gbl...
>> You didn't mention whether there are any indexes.  It might be worth a 
>> look...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>> news:CIxjj.442610$kj1.100173@bgtnsc04-news.ops.worldnet.att.net...
>>> Jeff,
>>>
>>> Thanks for your feedback.
>>>
>>> All of the data is inside this AC DB (no linked tables SQL Server or 
>>> external text/XL files).  Normally we set up a query to match our XL 
>>> templates, open the query, copy-n-paste from AC to XL.
>>>
>>> I'd like to keep that manual copy-n-paste procedure (don't want to 
>>> automate/program it) so I can see the data.
>>>
>>> The reason it is running so slow is because this client sends us this 
>>> DB. It is not a.....I forget the term....."standardized" DB:  there are 
>>> 12 tables with the exact same table definitions (number of columns, 
>>> column names, data types).  I don't want to put all the data in one 
>>> table because I want to leave the clients data unchanged and I don't 
>>> want to duplicate the data (taking up more disk space)......so I have 
>>> these 12 tables UNION ALL'ed together in a query (as well as 0's 
>>> converted to nulls using Iif() in the last 37 of 44 columns).  The 
>>> recordset of this UNION ALL query is 1.58 mil records.
>>>
>>> Then I use the 1.58-mil-records UNION ALL query in another query to look 
>>> up an order # and category (separate lookup tables).
>>>
>>> Then I use this "Order & Cat" query in a group by select query to 
>>> summarize data into 14 categories/order #'s, summing or counting the 
>>> last 37 of 44 columns, turning 1.58 mil rows into 57.
>>>
>>> It is from this 57-row summarized query that I copy-n-paste into XL.
>>>
>>> I'd like to keep this process the same, I was just curious as to how 
>>> long it was taking for these queries to run and I didn't want to sit 
>>> here and twiddle my thumbs, watching my watch, waiting for it finish. 
>>> It is maybe something like 5-10 mins.
>>>
>>> Thanks again for all of your help,
>>>
>>> Conan
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>> news:uYxAm1JWIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>> Conan
>>>>
>>>> Would you describe what having the query data will allow you to 
>>>> accomplish? You've described a "how" (open a query, view the results), 
>>>> but not the "why".  It may be that there are other ways to get done 
>>>> what you're after.
>>>>
>>>> NOTE:  "long running queries" may run long for several reasons, among 
>>>> them the possibility that the join, sort and/or selection fields are 
>>>> not indexed properly, and the possibility that data from non-local 
>>>> sources (e.g., linked SQL-Server table) is being subjected to functions 
>>>> that ONLY exist in the local (Access) application.  Do any of these 
>>>> apply in your situation?
>>>>
>>>> Good luck!
>>>>
>>>> Regards
>>>>
>>>> Jeff Boyce
>>>> Microsoft Office/Access MVP
>>>>
>>>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>>>> news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
>>>>> Hello all,
>>>>>
>>>>> Is it possible to time long running select queries?
>>>>>
>>>>> I was looking at creating some code to do this....something like:
>>>>>
>>>>> 1.  Store current time in a time variable
>>>>> 2.  Try to programmatically open a query ("open" meaning just like 
>>>>> manually opening a select query from MS Access to view data) using 
>>>>> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
>>>>> 3.  Check the current time and compare it to the start time saved at 
>>>>> the beginning
>>>>>
>>>>> But, I get "Run-time error '3219': Invalid operation." when I run 
>>>>> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
>>>>> OpenRecordset method is for opening a query in memory/the background 
>>>>> (storing in a Recordset object variable, not for viewing) rather than 
>>>>> actually opening a query to view the data).
>>>>>
>>>>> Thanks for any help anyone can provide,
>>>>>
>>>>> Conan Kelly
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Jeff
1/17/2008 11:45:30 PM
Conan -

You';re most welcome.  Glad it works for you.

Bob

Conan Kelly wrote:
>raskew,
>
>Thanks for the code.  That will do exactly what I want.
>
>Thanks again,
>
>Conan
>
>> Hi -
>>
>[quoted text clipped - 63 lines]
>>>>>>>
>>>>>>> Conan Kelly

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1

0
raskew
1/18/2008 12:01:17 AM
Jeff,

Thanks for the feedback.

I'll try to keep this in mind the next time I run into long running queries.

Thanks again,

Conan




"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:eIDCMMWWIHA.6044@TK2MSFTNGP05.phx.gbl...
> Conan
>
> Usually, a field that gets used for joining, for sorting, and/or for 
> selecting on can be indexed for faster query performance.  Of course there 
> are exceptions, and indexing is part art, part science... you usually have 
> to just try to see if it works better.
>
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
> news:1zLjj.142802$MJ6.7279@bgtnsc05-news.ops.worldnet.att.net...
>> sorry...there are no indexes.  Also, I don't quite understand how to use 
>> them/why they speed things up...so I don't really use them too much (in 
>> both AC & SQL Server).
>>
>> But we haven't had much of a need for them either.  Most of our DB's are 
>> just snapshots of clients data.  The are not live DB's that have many 
>> users in them looking up/changing data.  Usually there is one person in 
>> them (usually me) that just runs queries and copies results to XL files. 
>> And they get bulk inserts as often as every 3 months.  Everything we have 
>> runs pretty quickly, so I'm not too concerned with speed.  There are just 
>> a handfull that take longer than 5 mins to run, and if they take much 
>> longer, I find ways to speed them up (i.e. make temp tables out of 
>> query/view results, then drop them when I'm done.)
>>
>> Thanks again,
>>
>> Conan
>>
>>
>>
>>
>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>> news:uVBuL6RWIHA.4448@TK2MSFTNGP03.phx.gbl...
>>> You didn't mention whether there are any indexes.  It might be worth a 
>>> look...
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>>> news:CIxjj.442610$kj1.100173@bgtnsc04-news.ops.worldnet.att.net...
>>>> Jeff,
>>>>
>>>> Thanks for your feedback.
>>>>
>>>> All of the data is inside this AC DB (no linked tables SQL Server or 
>>>> external text/XL files).  Normally we set up a query to match our XL 
>>>> templates, open the query, copy-n-paste from AC to XL.
>>>>
>>>> I'd like to keep that manual copy-n-paste procedure (don't want to 
>>>> automate/program it) so I can see the data.
>>>>
>>>> The reason it is running so slow is because this client sends us this 
>>>> DB. It is not a.....I forget the term....."standardized" DB:  there are 
>>>> 12 tables with the exact same table definitions (number of columns, 
>>>> column names, data types).  I don't want to put all the data in one 
>>>> table because I want to leave the clients data unchanged and I don't 
>>>> want to duplicate the data (taking up more disk space)......so I have 
>>>> these 12 tables UNION ALL'ed together in a query (as well as 0's 
>>>> converted to nulls using Iif() in the last 37 of 44 columns).  The 
>>>> recordset of this UNION ALL query is 1.58 mil records.
>>>>
>>>> Then I use the 1.58-mil-records UNION ALL query in another query to 
>>>> look up an order # and category (separate lookup tables).
>>>>
>>>> Then I use this "Order & Cat" query in a group by select query to 
>>>> summarize data into 14 categories/order #'s, summing or counting the 
>>>> last 37 of 44 columns, turning 1.58 mil rows into 57.
>>>>
>>>> It is from this 57-row summarized query that I copy-n-paste into XL.
>>>>
>>>> I'd like to keep this process the same, I was just curious as to how 
>>>> long it was taking for these queries to run and I didn't want to sit 
>>>> here and twiddle my thumbs, watching my watch, waiting for it finish. 
>>>> It is maybe something like 5-10 mins.
>>>>
>>>> Thanks again for all of your help,
>>>>
>>>> Conan
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
>>>> news:uYxAm1JWIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>>> Conan
>>>>>
>>>>> Would you describe what having the query data will allow you to 
>>>>> accomplish? You've described a "how" (open a query, view the results), 
>>>>> but not the "why".  It may be that there are other ways to get done 
>>>>> what you're after.
>>>>>
>>>>> NOTE:  "long running queries" may run long for several reasons, among 
>>>>> them the possibility that the join, sort and/or selection fields are 
>>>>> not indexed properly, and the possibility that data from non-local 
>>>>> sources (e.g., linked SQL-Server table) is being subjected to 
>>>>> functions that ONLY exist in the local (Access) application.  Do any 
>>>>> of these apply in your situation?
>>>>>
>>>>> Good luck!
>>>>>
>>>>> Regards
>>>>>
>>>>> Jeff Boyce
>>>>> Microsoft Office/Access MVP
>>>>>
>>>>> "Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message 
>>>>> news:jWwjj.140803$MJ6.43064@bgtnsc05-news.ops.worldnet.att.net...
>>>>>> Hello all,
>>>>>>
>>>>>> Is it possible to time long running select queries?
>>>>>>
>>>>>> I was looking at creating some code to do this....something like:
>>>>>>
>>>>>> 1.  Store current time in a time variable
>>>>>> 2.  Try to programmatically open a query ("open" meaning just like 
>>>>>> manually opening a select query from MS Access to view data) using 
>>>>>> "Application.CurrentDb.QueryDefs("Query3").OpenRecordset 
>>>>>> dbOpenTable".
>>>>>> 3.  Check the current time and compare it to the start time saved at 
>>>>>> the beginning
>>>>>>
>>>>>> But, I get "Run-time error '3219': Invalid operation." when I run 
>>>>>> "...OpenRecordset dbOp...".  Also, I get the feeling that the 
>>>>>> OpenRecordset method is for opening a query in memory/the background 
>>>>>> (storing in a Recordset object variable, not for viewing) rather than 
>>>>>> actually opening a query to view the data).
>>>>>>
>>>>>> Thanks for any help anyone can provide,
>>>>>>
>>>>>> Conan Kelly
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Conan
1/18/2008 12:09:45 AM
Reply:

Similar Artilces:

Queries in Replicated Database
I have a database that is replicated. It has been working fine. I designed 5 new queries. When I right click and bring up the properties, the option to make the queries replicable is no longer lit. I have not had any problem before adn all other queries are replicable. What have I done that resulted in these new queries not being replicable? Realistically, the other ones shouldn't have been replicated. Replication is only intended for data in tables, nothing else. Your application should be split into a front-end (containing the queries, forms, reports, macros and modules), li...

Can't report misconduct because it's obscene
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I just tried to report a post as inappropriate, because it was a solicitation for viewing a pornographic video, and my e-mail got bounced! The reject message included this: "Reasons for rejection may be: obscene language, graphics, or spam-like characteristics." Pretty ironic, hunh? sv650kd@officeformac.com wrote: > I just tried to report a post as inappropriate, because it was a > solicitation for viewing a pornographic video, and my e-mail got > bounced! The reject message included this: > >...

Serialize method call, store it and run it later
Hello, I'm looking for the best way to store a method call so I can execute it in the future. I asume that the soap infraestructure can be useful because it can serialize the calls and deserialize them. I just need someone to point me to the start line (olympic expression by the way ;) ). Thanks Eduardo Mendez Depends on what you want One option would be to use COM+ and make the target object a "queued component". Method invocations will be queued (in MSMQ). Replay happens "later". If the method is being invoked on a webservice, you could introduce an MSMQ or ...

Year-End Closing Queries
Hello, I read a paper about Year-End Closing Process and mentioned about two queries sentence in the database maintenance process: reindex.sql and recomp.sql. These queries are located in \Microsoft Dynamics\SQL\Util\, but I don't have these queries in my installation. Can you explain me these queries?, Anyone has these queries?. My GP version is Microsoft Dynamics GP 9.0 Thanks. Kind Regards, Alfredo In version 9 and earlier, these scripts (and the folder) were only found on the "server" install. This is the workstation where GP Utilities can be run. Later versions of 10 wi...

Can I use a german product key on an English CD?
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) I have bought MS mac 2008 in Austria so the UI is in German however I would like it in English. I understand that it cannot be changed but I was wondering whether if I borrowed a friend's English installation CD, would my german product key work? or is the product key specific to the language? If this doesn't work, could you tell me if the 2008 version is really worth it (I have the 2004 version installed).. I only have basic german and may not understand all the menus and dialog boxes. Thanks for your help! <chalmin@office...

Reclaimable Disk Space After Running Mailbox Manager
After years of never having any limits on mailbox size and not deleting old messages in Inbox and Sent after a period of time, we are planning on setting up such limits using Mailbox Manager. My question is this: after we create the policy and Mailbox Manager deletes all the older messages and attachments, will there be a corresponding increase in available disk space? Or will all that deleted content simply become "white space" within the store and not free up any disk space? Scott you will have lots of whitespace in the store after DIR has expired on all those items...only ...

Oulook logging the wrong time
When I send a message from Outlook, the recipient within the same office gets it as being sent an hour later. The client sending the mail has the clock set for the right time and the time zone is also correct. No other computers in the office are having this issue. Thanks Sorry - forgot to check the savings time "boe" <boe_d@hotmail.com> wrote in message news:Of20toraEHA.3988@tk2msftngp13.phx.gbl... > When I send a message from Outlook, the recipient within the same office > gets it as being sent an hour later. The client sending the mail has the > clock set...

Entering times into an Excel spreadsheet
I'm trying to create a column in a spreadsheet for a "time" - 24 hour clock showing hour and minutes. I've tried formatting the cell as a time, and also as a "custom" cell - however, every time I enter the data and press return, the figures all change. Any ideas??!! Look if you have fixed decimal set under tools>options>calculation -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "DavidLilley" <david@lilley1256.fsnet.co.uk> wrote in message news:c9ist9$knu$1@news8.svr.pol.co.uk... > I'...

How do I set up a new email address for the first time?
I'm new to using Outlook and need to set up an email address for the first time. It cannot find my settings automatically and I'm not sure what to do with the manual settings. Can someone please help?? Contact your ISP or check their website for the correct account and server settings. You have to set up your Outlook profile manually: http://www.howto-outlook.com/faq/newprofile.htm -- Kathleen Orland "New email address set up" <New email address set up@discussions.microsoft.com> wrote in message news:93C050E0-2C56-4C1D-870F-B6084E1CA3FF@microsof...

Long category labels will not expand
Long category labels space does not expand for more of the label to appear in one chart among a group of charts. In other charts in the same document, the longer labels appear correctly. ...

IIf Function help with my Query
First some background, I have two tables called 2007 Grid and 2008 Grid, in these tables I have a field called Census Rec'd. For my query, I need to create a field called Census Rec'd and I need this field to do the following - If "Plan Year" ends in 2007 from "AFTAP Table" then insert Census Rec'd from the 2007 Grid and/or if "Plan Year" ends in 2008 from AFTAP Table then insert Census Rec'd from the 2008 Grid. Any suggestions? I suggest you change your database design. Having a table for each year is incorrect. You are starting to se...

How can a person find out what style or styles are being used?
Hello Everyone, I open a MS Publisher 2007 document and don't know what type of formatting it is using for the heading and the body of text. Is there a way to know what styles are being used in the document? In the menu bar under "Format" a person could check the settings in "Font", "Character Spacing", "Paragraph", "Bullets and Numbering", "Horizontal Rules", "Tabs", etc. and get an idea of the formatting and possibly get an idea of the style, but what if the document was created by someone else and ...

Can send but can't receive No.2
Further to Merc's post. Ditto me. Some emails to my account not received from server. On synching, just get the result "No new messages". (checked and they are not in the server's Spam folder, but still in the server's in box) (all accounts are set to leave mail on the server until deleted from Deleted Items folder) But apparently all mail addressed to mywife@xxx.com arrive in my wife's account and also in my account. However, WinMail receives all mail not spammed by the server. (all accounts are set to leave mail on the server until deleted from De...

how can i convert a .pub file to
i got two .pub file from my uncle. but i don't have ahe publisher in my office package. so how can i read those file .how can i convert thoses file into some others files. plz it's rgent . ps. i can't send it to you. i have to solve in my pc. Have your uncle send you the files in pdf form. Or, you will need to buy Publisher and then be able to open the files. Or, have him print them out and send by the mail man. -- Don Vancouver, USA "tokon" <tokon@discussions.microsoft.com> wrote in message news:AF0FA107-72D0-4994-B73A-AB5B43FD8D44@microsoft.com... >...

Can this VBA be translated to VBScript?
Thanks. I took out the Dim statements - but when I do that the statement p.MyContacts fails. ======= Const MPHONE_TYPE_WORK = 1 Sub showContacts() Dim p As CommunicatorAPI.Messenger Dim s As CommunicatorAPI.IMessengerContacts Dim t As CommunicatorAPI.IMessengerContact Dim o As CommunicatorAPI.IMessengerContact Dim w As String Dim v As Long v = 1 w = "" Set p = CreateObject("Communicator.UIAutomation") p.AutoSignin Set s = p.MyContacts For v = 1 To s.count - 1 Set o = s.Item(v) Next v ...

Make excel run large spreadsheets faster
For those of you who run large spreadsheets with or without VBA, I think everyone will agree they are much too slow ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=e1105194-24af-4697-ab5f-b9d62e5140ea&...

Run-time error '-2147217900 (80040e14)'
I have a tree list control I want to populate with Categories based on a Client. The client number is input into a text box & then the update event populates the tree based on the selection. I get a syntax error when it tries to open the recordset based on the SQL. Two tables are invloved (Client & Categories; in a 1-to-many relationship on pID field). Here'e the code: Dim rstCategory As New ADODB.recordset, rstComponent As New ADODB.recordset Dim rstSubComponent As New ADODB.recordset Dim tvwTree As Object Dim nodX As Node Dim I As Integer Dim blnAllRecs As B...

Date and Time Chart
I have data that consists of a due time, a submitted time, and 3 types of job categories. Essentially, what I would like to have is the date along the x axis, the jobs on the primary axis, a secondary axis with times, a bar chart with the types of jobs and the due times and submitted times to be trend lines in the same graph. Is this possible? If so, what is the best way possible to achieve this? Thank you! You should be able to find something here that helps. http://www.peltiertech.com/Excel/Charts/ComboCharts.html "KC8DCN" <KC8DCN@discussions.microsoft.com> wrote i...

Can't E-mail excel's anymore, Why?
I use Office 97, which I used for years on a Win95 machine with no trouble. In November, I bought a new computer, P4 2.66 GHZ, 512 DDR Ram, 120 Gb, Win XP. No trouble from then until a few days ago. First, the option to "send to" on the File menu disappeared, so I couldn't E-mail from there, but I could still E-mail from the folder the files were saved to. Today, When I try to send from that folder, i get " Microsoft Exchange Setup Wizard", which tells me I'm set to use "Corel Central Address Book 9". I have Corel on my computer, but I swear I...

Graphing A Time Series OF Stocks
My data looks like so: Stock TradePrice TradeVolume ExecutionDateTime EFG 5.2 8 2:40:04 PM EFG 5.5 8 2:40:02 PM EFG 5.1 10 2:40:01 PM EFG 5.3 10 2:40:01 PM EFG 5.1 10 2:40:00 PM EFG 5.1 10 2:40:00 PM ABC 1.5 4 2:31:17 PM ABC 1.6 ...

can i convert a word document into an excel spreadsheet?
i would like to convert a word document into an excel spreadsheet. "jodibaldwin" <jodibaldwin@discussions.microsoft.com> wrote in message news:8F32216D-9070-4AEA-BFBC-58ADDFAFE53B@microsoft.com... > i would like to convert a word document into an excel spreadsheet. Try saving as a text file and opening that in Excel. ...

Can you abort a send-in-process for a too-large message?
Running OL2007 under Vista Home Premium. BudV;143450 Wrote: > Running OL2007 under Vista Home Premium. Sure. Go to File, Work offline. Wait 5 min then delete the file and go back online. -- Slipstick 'Outlook & Exchange Solutions Center' (http://www.slipstick.com) 'Outlook Tips' (http://www.outlook-tips.net/) -- http://forums.slipstick.com ...

window user/group can't see reports
Hi, I login as admin I can see folder/reports, but when login as normal user, it's a blank home page, can someone help here? thanks. RS has its own security that is role based. By default the local administrators group on the server is added to the admin role in RS. What you need to do is to add users or groups to the appropriate role in Reporting Services. Read up on Roles in books online and it will tell you how to go about this. If you set the role at the home level then all the folders under that will inherit from home. -- Bruce Loehle-Conger MVP SQL Server Re...

Complex Counting Query
Hey folks, I've got a query here that's killin' me, and I was hoping someone could offer some suggestions. I have two tables... Table A Field1: Date (no null values) Field2: Consult Number (Alphnumeric - no nulls) Field3: Product (Alphabetic - no nulls) Field4: Working Type (Alphabetic - no nulls) Table B Field1: Date (no null values) Field2: Consult Number (Alphnumeric - no nulls) Field3: Product (Alphabetic - no nulls) The relationship between the two tables is that they both have the same group of products in them Table A has several different Products, each of which has ...

Can RMS apply discounts by date?
We are looking to implement a POS system across 5 locations and a central warehouse. Long store short, we process all of our own merchandise, price and tag it, and ship it to the stores. We would like to have merchandise discounted, by the POS software, after a set time that the price tag was printed. Basically the price tag would tell the POS the Department, Price, and Date of print. My question is, can RMS deal with the date in the barcode and apply discount policy automatically. Example Department A, Price 5.00, Date 12-01-09, so the plu/barcode would say something like A000500120109. I gu...