Querry run time display

I am New to VBA i have this querry below which works fine how can i add the 
start time and end time and then show how much time it too to run?

thanks

Public Function RunTableToInsert() As Boolean
    Dim db As DAO.Database
    Dim qdDelete As DAO.QueryDef
    Dim qdInsert As DAO.QueryDef
    'Dim pStatus As DAO.Parameter
    
    Set db = CurrentDb
    Set qdDelete = db.QueryDefs("qdelShipToMaster")
    Set qdInsert = db.QueryDefs("qappShipToMaster")
    'Set pStatus = qdInsert.Parameters("status")
    'pStatus.Value = strStatus
    
    qdDelete.Execute
    qdInsert.Execute
    
    
End Function


0
Utf
4/2/2010 2:03:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
937 Views

Similar Articles

[PageSpeed] 34

Hi Octet32,

modify your code in this way and the trick is done

Public Function RunTableToInsert() As Boolean
    Dim db As DAO.Database
    Dim qdDelete As DAO.QueryDef
    Dim qdInsert As DAO.QueryDef
    'Dim pStatus As DAO.Parameter
    Set db = CurrentDb
    Set qdDelete = db.QueryDefs("qdelShipToMaster")
    Set qdInsert = db.QueryDefs("qappShipToMaster")
    'Set pStatus = qdInsert.Parameters("status")
    'pStatus.Value = strStatus
    start_time=format(now,""hh:mm:ss")    
    
    qdDelete.Execute
    qdInsert.Execute
    end_time=format(now,""hh:mm:ss")    
    msgbox "Elapsed time " & end_time-start_time
    
End Function

this is untested so try it.

HTH Paolo

"Octet32" wrote:

> I am New to VBA i have this querry below which works fine how can i add the 
> start time and end time and then show how much time it too to run?
> 
> thanks
> 
> Public Function RunTableToInsert() As Boolean
>     Dim db As DAO.Database
>     Dim qdDelete As DAO.QueryDef
>     Dim qdInsert As DAO.QueryDef
>     'Dim pStatus As DAO.Parameter
>     
>     Set db = CurrentDb
>     Set qdDelete = db.QueryDefs("qdelShipToMaster")
>     Set qdInsert = db.QueryDefs("qappShipToMaster")
>     'Set pStatus = qdInsert.Parameters("status")
>     'pStatus.Value = strStatus
>     
>     qdDelete.Execute
>     qdInsert.Execute
>     
>     
> End Function
> 
> 
0
Utf
4/2/2010 2:26:01 PM
Sorry but I used an extra quote so start_time and end_time must be as follow

start_time=format(now,"hh:mm:ss")    
end_time=format(now,"hh:mm:ss")    
and is better if you format also the canculation for the elapsed time so
msgbox "Elapsed time " & format(end_time-start_time,"hh:mm:ss")

Cheers Paolo

"Paolo" wrote:

> Hi Octet32,
> 
> modify your code in this way and the trick is done
> 
> Public Function RunTableToInsert() As Boolean
>     Dim db As DAO.Database
>     Dim qdDelete As DAO.QueryDef
>     Dim qdInsert As DAO.QueryDef
>     'Dim pStatus As DAO.Parameter
>     Set db = CurrentDb
>     Set qdDelete = db.QueryDefs("qdelShipToMaster")
>     Set qdInsert = db.QueryDefs("qappShipToMaster")
>     'Set pStatus = qdInsert.Parameters("status")
>     'pStatus.Value = strStatus
>     start_time=format(now,""hh:mm:ss")    
>     
>     qdDelete.Execute
>     qdInsert.Execute
>     end_time=format(now,""hh:mm:ss")    
>     msgbox "Elapsed time " & end_time-start_time
>     
> End Function
> 
> this is untested so try it.
> 
> HTH Paolo
> 
> "Octet32" wrote:
> 
> > I am New to VBA i have this querry below which works fine how can i add the 
> > start time and end time and then show how much time it too to run?
> > 
> > thanks
> > 
> > Public Function RunTableToInsert() As Boolean
> >     Dim db As DAO.Database
> >     Dim qdDelete As DAO.QueryDef
> >     Dim qdInsert As DAO.QueryDef
> >     'Dim pStatus As DAO.Parameter
> >     
> >     Set db = CurrentDb
> >     Set qdDelete = db.QueryDefs("qdelShipToMaster")
> >     Set qdInsert = db.QueryDefs("qappShipToMaster")
> >     'Set pStatus = qdInsert.Parameters("status")
> >     'pStatus.Value = strStatus
> >     
> >     qdDelete.Execute
> >     qdInsert.Execute
> >     
> >     
> > End Function
> > 
> > 
0
Utf
4/2/2010 2:35:02 PM
Thanks Paolo

"Paolo" wrote:

> Hi Octet32,
> 
> modify your code in this way and the trick is done
> 
> Public Function RunTableToInsert() As Boolean
>     Dim db As DAO.Database
>     Dim qdDelete As DAO.QueryDef
>     Dim qdInsert As DAO.QueryDef
>     'Dim pStatus As DAO.Parameter
>     Set db = CurrentDb
>     Set qdDelete = db.QueryDefs("qdelShipToMaster")
>     Set qdInsert = db.QueryDefs("qappShipToMaster")
>     'Set pStatus = qdInsert.Parameters("status")
>     'pStatus.Value = strStatus
>     start_time=format(now,""hh:mm:ss")    
>     
>     qdDelete.Execute
>     qdInsert.Execute
>     end_time=format(now,""hh:mm:ss")    
>     msgbox "Elapsed time " & end_time-start_time
>     
> End Function
> 
> this is untested so try it.
> 
> HTH Paolo
> 
> "Octet32" wrote:
> 
> > I am New to VBA i have this querry below which works fine how can i add the 
> > start time and end time and then show how much time it too to run?
> > 
> > thanks
> > 
> > Public Function RunTableToInsert() As Boolean
> >     Dim db As DAO.Database
> >     Dim qdDelete As DAO.QueryDef
> >     Dim qdInsert As DAO.QueryDef
> >     'Dim pStatus As DAO.Parameter
> >     
> >     Set db = CurrentDb
> >     Set qdDelete = db.QueryDefs("qdelShipToMaster")
> >     Set qdInsert = db.QueryDefs("qappShipToMaster")
> >     'Set pStatus = qdInsert.Parameters("status")
> >     'pStatus.Value = strStatus
> >     
> >     qdDelete.Execute
> >     qdInsert.Execute
> >     
> >     
> > End Function
> > 
> > 
0
Utf
4/2/2010 3:19:02 PM
Depending on the execution speed you may want to get down to the millisecond 
for that you'd need to use the following API

http://allapi.mentalis.org/apilist/timeGetTime.shtml 
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Octet32" wrote:

> I am New to VBA i have this querry below which works fine how can i add the 
> start time and end time and then show how much time it too to run?
> 
> thanks
> 
> Public Function RunTableToInsert() As Boolean
>     Dim db As DAO.Database
>     Dim qdDelete As DAO.QueryDef
>     Dim qdInsert As DAO.QueryDef
>     'Dim pStatus As DAO.Parameter
>     
>     Set db = CurrentDb
>     Set qdDelete = db.QueryDefs("qdelShipToMaster")
>     Set qdInsert = db.QueryDefs("qappShipToMaster")
>     'Set pStatus = qdInsert.Parameters("status")
>     'pStatus.Value = strStatus
>     
>     qdDelete.Execute
>     qdInsert.Execute
>     
>     
> End Function
> 
> 
0
Utf
4/2/2010 4:46:01 PM
Reply:

Similar Artilces:

no row numbers or column letters DISPLAYED
Help! I have one document where I can't see my row numbers or column letters on my spreadsheet. This makes it difficult to select rows or columns to cut and paste elsewhere. Is there a way to display the column letters and row numbers (not on printed output, but on monitor)? This document was created by a former co-worker and I have to modify it every year. There are numerous formulas and details to start from scratch. <Tools> <Options> <View> tab, And make sure to *CHECK* "Row & Column Headers". -- HTH, RD ====================================...

owa not displaying messages
hi when using ie6 owa is diplaying the folders ok but the 2 panes for viewing the messages and viewing an individual email content is blank with 'loading.....' message in one of the blank panes. It never actually loads anything into these 2 panes. Am using IE 6, sp2, xphome, msoffice 2003 mozilla firefox 1.5 and opera 9 display ok. tks, Dave it's a problem with the active x control - try deleting the control and clearing the cache (from IE's tools, options dialog) and restarting the browser. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours ...

Open all Excel spreadsheets/fles before running queries
I have a number of spreadsheets which use Ms Query to retrieve data from an Oracle database. I have set it up so that queries run automatically when I open a spreadsheet. In Excel 2003, if I open several such spreadsheets at the same time, Excel will open all spreadsheets first and then run the queries. This means that I can open all the spreadsheets I want to update, get immediate notification if any one of them is in use by someone else, open as read-only if so, and then go off and do something else while all queries update (15-20 minutes in some cases). In Excel 2007 wh...

Time-out for InputBox
Sub InputBoxTimeout() ' How do I add a time-out conponent to this? Someone please Range("A1") = InputBox(prompt:=" Enter a number within 1 minute," & vbLf & " otherwise, 4 will be assumed.") End Sub Best way is to have a little userform that you load with a textbox for input. In the userfor activate, add this code Application.Ontime Now + TimeSerial(0,1,0),"KillForm" and then have a sub in a standard code mode Public Sub KillForm() Unload Userform1 End SUb -- HTH RP (remove nothere from the email address if mailing direct)...

Adding time #2
I am trying to add time and I am having some difficulties finding a way to format my numbers to be on a time scale. Example: John worked Monday 8.02 Tuesday 8.0 Wednesday 8.54 Thursday 8.09 Friday 7.59 This would total to 41.04 hours for the week but if I try to add this on Excel it is using the number scale of 100 instead of a time scale of 60 so I am getting a total of 40.24. Is there any way to format this to add it correctly?? >Monday 8.02 Is 8.02 supposed to be 8 hours and 2 minutes? If so, why don't you just enter those values as *time*: 8:02 8:00 8:54 8:09 7:59 =SUM...

Streaming PDF, measuring response timing
Dear all I have an asp.net application that streams PDFs to the browser in 64kb chunks, looping through until all the PDF is streamed out. Here's a few lines to help you get the picture (at bottom of this post) My question is that I'm going to measure the time that this code takes to run and log it to see how things are performing. However, what will this measurement actually be representing? a) The time it takes for the whole data of the PDF to reach the client. b) same as a) but additional time it takes for the Adobe Reader to load and display (which is usually sl...

Editing time that has its colon removed
I have a large CSV file that has the time column stripped of its colon meant to distinguish hours and minutes. ie. 9:20 is displayed 9200. 23:55 is displayed as 2355 Is there a way of adding a colon automatically in the right spot? I have over 875000 of these to do. Question... given how 9:20 is changed to 9200, what would 1:20 and 12:00 (and, depending on how you show it, maybe 0:12 as well) be changed to? -- Rick (MVP - Excel) "rhhince" <rhhince@gmail.com> wrote in message news:cc567b14-1544-4bfc-a69c-7e1169a7d3a2@37g2000vbn.googlegroups.com... ...

25th percentile (cycle time) 11-19-09
Here is a scenario: If: Column A is list of AUTO MAKERS Column B is list of DEALERSHIPS Column C is list of dates when DEALERSHIP OPENED Column D is list of dates when DEALERSHIP READY TO SELL CARS Column E is the the difference of Column D and Column C (D-C) (Cycle Time) There are a thousand rows and multiple column in this data set. I would like to find out how long does it take the 25th percentile of any DEALERSHIP to get READY TO SELL CARS. Please share your thoughts. Rum Hi, For column E I used =DATEDIF(C2,D2,"d") to calculate the difference in d...

Can not running recovery batch
When I post a employee expenses batch, the batch posting was suddenly interrupt, so I go to recovery batch window find this batch, then I selected this batch with status interrupt posting batcn, and runing continue, but the recovery does not running, there is still a massage 'Batchxxxxx failed to complete posting. Use the Batch recovery window to complete the posting process' and more infor in error message is 'A save operation on table'pmt_transaction_OPEN' has created a duplicate key.' Who can tell me why this happen, and how could I solved this problem? ...

No message box displayed
Hello. I am running a macro that save my files in Excel 4 format after doing what have to be done ( I have more then 50 files). However, Excel is always warning me that the file that i want to save is in Excel 4 format and I always need to press the ok button. I am sure that there is a way for me to tell Excel that I do not want any message displayed (that will necessitate my intervention) when the macro is running. Can you please help me out? Thanks for your help! Untested: application.displayalerts = false 'your code to save as xl4 application.displayalerts = true Marc wrote: >...

Can you recommend some free real-time block list service?
Hi! Can you recommend some free real-time block list services? (Exchange Server 2003) Thanks! Iv Michael wrote: > Hi! > Can you recommend some free real-time block list services? > (Exchange Server 2003) sbl-xbl.spamhaus.org no-more-funn.moensted.dk cn-kr.blackholes.us dnsbl.sorbs.net blackholes.five-ten-sg.com Thanks! One more question. I'm add all this providers (masks 127.0.0.3, ...5, ...7, ....9). But in Perfomance logs (Connections Rejected by Block List Providers) after 2 hours still zero. For example, "Messages Filtered Due to Blank Sender" = 20. It's OK...

Display name in Active Directory
My display name in Active directory uses the initial of my company as my first name hence, email sent to me says it's to: asg- brian yadda yadda. How do I get my email to say it's to me without changing Active Directory? I'm not sure you can. Why did you precede the display name with the company name? Nue "badamsios" <brian@brianandmaryadams.com> wrote in message news:1145923537.129809.267920@y43g2000cwc.googlegroups.com... > My display name in Active directory uses the initial of my company as > my first name hence, email sent to me says it's to: asg...

Delivery Issue: Messages Do Not Display the Name of the Sender in the 'From' Field (Folder List) but Are Fine when Opening Them
Hello, One of our users received two strange messages from me and another co-worker. The messages are correct when opening them: the sender's names are there. But in Folder List view, the From field is blank. At beginning, we thought it was an Outlook issue. But it was same when he checked from another machine. Actually, I can see the blank From field message when I open his inbox when using my Outlook. Those two messages were sent from mailboxes on one Exchange server to mailbox on another Exchange at almost the same time. And everything is fine before and after those two messages...

Portfolio manager and same security display problem
I am using Money 2008 Plus Deluxe. I have investment in Mutual Fund which is held partly directly with the Fund Manager and partly with a Fund supermarket. There are two seperate accounts list to manager this. In the Portfolio Manager, I want to show these as seperate items but Money will not allow me to use the same symbol for it. Is there a way round this? Alternatively, how can I 2group" both these accounts as one in Portfolio Manager? In microsoft.public.money, nospam@aol.com wrote: > >I am using Money 2008 Plus Deluxe. > >I have investment in Mutual Fund which is ...

Non working time SQ DB
Hi, Do you know where in Project Server 2007 DBs stored Standard calendar non working times? e.g. weekends, hollydays and so on... Thanks!! Hi Yoav, on resource level you can retrieve that information from MSP_EpmResourceByDay_UserView. 'Base Capacity' are hours per day for each user, based on calendar. 'Capacity' will give you information if absence was entered in timesheet or if working time was changed for that user. Does that help? Regards Barbara "Yoav" <Yoav@discussions.microsoft.com> wrote in message news:AB21EE3C-CBF6-40FD-A958-B...

Run-time error on setting textbox
I am trying to set a value to a textbox on a subform from a selection in a popup form. The textbox is bound to a table field (which is a foreign key / number field). The listbox on the popup returns a number. When I do: Forms!frmMainform!frmSubform.Form!txtNumber = Me.lstbox I get the following error: Run-time error 2448 You can't assign a value to this object I have another very similar form where I am able to set a value into a bound textbox. Why, then, can't I do it on this form? Is there some property that is set wrong or something? I need to be able to set this valu...

Get Appts to display in current time zone?
I have a bunch of appts that were added to my calendar from a web site (PDC site) while I was in Central time zone. These appts were added and are displayed in Central time. Now I'm in LA for the conference and have reset the time on my computer to Pacific time; however these appts are STILL displayed in Central Time! How do I get them to display in Pacific time? This is Outlook 2007 running on Win7. The appts display with the time zone drop downs showing. Thanks in advance, Bill "Bill Cohagan" <BillCohagan@discussions.microsoft.com> wrote in message ...

Running delete query from Excel
Hi there, From Excel I use ADO to export the Excel data to an Access table. Before the new data may be imported, this Access table needs to be cleared, To accomplish this I have created a delete query that deletes all records from the table. Currently I have to run this query manually before executing the ADO from Excel. Is it possible to run the delete query from Excel using VBA? -- ���`�.(*�.�(`�.� �.��)�.�*).��`�� �.............. CHARLIE ..............� ���`�.(�.��(�.�* *�.�)`�.�).��`�� Hi Charlie I assume you have already opened a Connection object on your databas...

How to Increment Time using EXCEL ??
I'm using, and love it, Excel 97. I want to know how to increment TIME. Say I have a few rows, each start at 10:00 AM and I want each to increment at different intervals. Let's say row 1 is 15 min, row 2 is 17 minutes, row 3 is 60 minutes, row 4 is 75 minutes, etc..etc.. I cannot find documentation about this within Excel NOR on Microsoft's web site. Does anyone of you know how to do this? I'd sure appreciate the help. thanks, Time is based on 1 = 1 day. In cell C1 I put =24/60 and format as a number - this equals 1 minute In cell A4 I put =00:15 and format as time - ...

Unhide more than one worksheet at a time
I have a worksheet that contains 50 hidden worksheets. Is there a method or macro that somebody knows of where I can unhide more than one workseet at a time? Thanks, Dave =@===----¬----¬----¬.¸¸.·´¯`·.¸¸.·´¯`·.¸.·<º))))>< Dave, Try the following code: Sub AAA() Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets WS.Visible = True Next WS End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "DBavi...

Folder displaying incorrect item number
I've got a number of users who have a shortcut to a public folder on their outlook bar. This shortcut incorrectly displays the number of items in the folder. For example, there is one item in the folder but the shortcut shows 52. If there's 2 items then it shows 53. If I log onto the PC then it displays the item number correctly. I logged one of the users onto another PC but the problem was still there. I recreated her network profile and her outlook profile but no joy. Were using outlook XP and XP desktop. Any help is appreciated Thanks Jeff Have you seen if there is any filter ...

Subform Flicker
I have a form with two subforms that are "in synch" with each other - the bottom form displays records based on the selection in the first form. The second, synchronized, subform allows data entry. Everything works fine. All data entry gets saved properly. EXCEPT every time I enter data on the subform, there is a brief flicker before the cursor moves to the next field. The record navigation buttons quickly disappear and then reappear. There is NO conditional formatting on the forms (I DID have some, but removed it just in case it's contributing). The first subform (sfrmTG1...

Domain logon required 2nd time when I try to view mail in Outlook2003/WinXP-SP2
WinXP SP2 did not fix the following problem: I am prompted for domain user name and password a second and sometimes a third time even though I had logged on to the SBS domain. This happens when I try to get my e-mail messages with my client's Outlook 2003 / WinXP SP2 System where these messages are stored on SBS Exchange. This dows NOT happen with my WinXP or WinXP SP2 / Outlook 2000 client systems Knowledge Base Article #820863 did not provide a fix for my computer. Couple of thoughts... 1) Check the event logs to see if the machine has lost its trust relationship. (Wi...

Still showing numbers not text in display
Since my previous post I went through both tables and did the lookup wizard on all the fields and selected only the RATING not ID. When I pull both tables into my query it shows the number for some and the word for others. The frustrating part is they were all created the exact same way so why are they displaying differently? I checked all the properties on both tables and everything is exactly the same and the information is still displaying in 2 different formats. Help before I pull all my hair out... =( Thanks for the quick responses by the way! Also... When I pull each...

Display > sign on a chart
How do you display > or < signs on a chart, I am trying to put values on a chart but some of them are <2 or >60 etc. These values are displayed as zero on the chart. Is it possible to display values with < or > signs before the number? Try using a custom number format. Go to Format -> Cells. On the Number Tab choose Custom from the Category option. Type in: <#,##;>#,## -- John Mansfield "Felicity" wrote: > How do you display > or < signs on a chart, I am trying to put values on a > chart but some of them are <2 or >60 etc....