UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> keeping form stable while processing large events    
 
   
artpolk
post Jul 1 2010, 08:16 AM
Post#1



Posts: 59
Joined: 31-December 09



This post relates to a form in an Access Data Project, but the overall issue is probably a general VBA one.
I have to load a lot of files to the SQL server using an interface I built in Access. I'm able to send the files into a string and the server handles them well. The problem is that while the event is processing the app appears to be unresponsive. You cannot click anything, and I can't update the status (percent complete, est remaining time, etc) while it's processing. Of course it's not broken. If I let it sit until it's done and I don't touch anything it always sucessfully executes the stored procedure. Some of these jobs take a minute or less to run, but some of the large files with more complicated sync processes might take 10-15 minutes. The problem is that the user has no idea when they should keep waiting or when it actually crashed. What is the best way to handle this? The event is usually a button click and it's executing a command object. It seems that there are two things I'd like to do:
#1 absolutely necessary that I find some way to allow the user to click a cancel button, and to display the elapsed time. I can split the file into chunks, if that's necessary, and have it check in to see if the user clicked cancel between each chunk. I actually intend to do the chunking anyway because we have a few files that have 2M+ records and they're a little too big to load into memory and process in one string. My theory is that we can process a chunk, and between each step calculate the percentage complete, estimated time remaining, and check if the user clicked cancel.
#2 it would be cool, but not required, that I could somehow run these events in the background and allow other parts of the app to continue to function.
My boss had said that in C# he usually uses multi threading to accomplish this, but from what I understand that is not an option in VBA. If what I'm trying to do is not possible, we might just move our import/export/sync processes out of the ADP and he'll build me something in C# that my ADP will just launch.
Go to the top of the page
 
pere_de_chipstic...
post Jul 1 2010, 08:24 AM
Post#2


UtterAccess Editor
Posts: 10,112
Joined: 8-November 07
From: South coast, England


Hi artpolk
handle long processes like this where the code is either in a loop, or has disinct sections, of code by opening a pop-up form, with a progress meter. As each loop, or code section completes the progress meter is updated showing progress.
The pop up form also includes a Cancel button; in each loop, or end of a code section, there is a DoEvents command that allows the code to check if the cancel button has been clicked and either continues or exits as appropriate.
hth
Edit: Attached A2003 format demo, Open the form 'frmOpen' and click button.
Attached File(s)
Attached File  ProgressMeter.zip ( 19.11K )Number of downloads: 29
 
Go to the top of the page
 
LPurvis
post Jul 1 2010, 09:33 AM
Post#3


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


How do you execute the procedure in question?
(It's a T-SQL proc I presume?)
Cheers.
Go to the top of the page
 
artpolk
post Jul 1 2010, 12:45 PM
Post#4



Posts: 59
Joined: 31-December 09



Basically the user selects the type of file they're importing and selects a file or directory (some files support a batch mode where they user can execute numerous files over and over). Once they have a list of the file(s) they want to process they click Import and it validates the file formats and executes a Microsoft SQL server stored procedure using a command object. Basically we send the entire file in a great big text string that is an input parameter in the stored procedure. I'm going to modify the stored procedure so that it will work with multiple chunks that build the string rather than requiring the whole string at once. Some of the process that just update matches and add new rows can accept partial files, but some of the processes are sync files where it needs the entire list to identify deletes.
tried earlier to pop up a form immediately before executing the stored procedure, but it still acts like it's not responding while it's executing. for most of these jobs 90% of the time is the upload time. There are a few complex stored procedures that are inserting/deleting/updating and validating against numerous tables, some with millions of rows. Those SPs might take 4 or 5 minutes to run.
Go to the top of the page
 
artpolk
post Jul 1 2010, 12:52 PM
Post#5



Posts: 59
Joined: 31-December 09



Thios looks great! It seems like DoEvents is the perfect solution for me. Really nice job on that progress meter!
Go to the top of the page
 
LPurvis
post Jul 1 2010, 01:01 PM
Post#6


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


The progress meter is part of your way there. But you need an iterative process within which to determine progress (the DoEvents give you the opportunity to process events between those iterations).
Is for the huge time lag itself (where you application is tied up) - you're right, with a single thread you'll be waiting.
However, have you tried executing the SP asynchronously?
It would at least give you the chance of the application being able to continue.
You could use ADO events to determine when it has completed and just inform the user (who has been going about their business in the mean time).
Cheers.
Go to the top of the page
 
artpolk
post Jul 1 2010, 01:22 PM
Post#7



Posts: 59
Joined: 31-December 09



DoEvents combined with smaller file chunks should help. Because of the extra load we obviously want to send the file in the largest reasonable chunks rather than line by line Insert Into statements. If we were doing line by line then the sample pere sent would probably work perfectly. Even with large chunks it's probably going to help a lot. Most of the files I'm currently using are from 1MB to 20MB, but I was just sent a sample of a worst case scenario 750MB file with 2M records. If I want to be able to process the worst case scenario I need to stop trying to get the whole file into memory and sending a single string. Just need to find that sweet spot for largest possible chunk that remains efficient.
I have not tried executing asynchronously. How exactly do I go about doing that? Is it a concept where once the string is uploaded I let go of the process and check in from time to time to see if it has completed? It does seem silly to tie up the local resources once the file is uploaded. At that point we're just waiting for it to process and return an error message or return code that says it completed. The sync and import processes would be a lot smoother if I could do something like that. Splitting the files into chunks and using DoEvents while uploading, and then letting go of the connection and checking back in for a status would make things much better.
You've helped me out before so you know - I consider myself a novice when it comes to Access-SQL connections and VBA. In both areas I know basic ways to get most things done, but I don't fully understand all of the options that are available to me and don't always know the BEST way.
Go to the top of the page
 
artpolk
post Jul 1 2010, 03:33 PM
Post#8



Posts: 59
Joined: 31-December 09



Here is my basic process now:
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strStoredProcName
.Parameters.Append .CreateParameter("RETURN_VALUE", adDouble, adParamReturnValue)
.Parameters.Append .CreateParameter("@SessionId", adGUID, adParamInput, , gblSessionId)
.Parameters.Append .CreateParameter("@string", adVarChar, adParamInput, 2000000000, strOutput)
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute
End With
Ogot the impression that I change .Execute to .Execute , , adAsynchExecute. I'm not sure what I do after that. I assume I need to have it check the state from time to time and eventually release the thread? This seems promising!
Go to the top of the page
 
artpolk
post Jul 1 2010, 04:09 PM
Post#9



Posts: 59
Joined: 31-December 09



You guys pointed me in the right direction! I just added that one word to my cmd.execute and 3 lines of code and it's performing beautifully!
Do While (cmd.State And adStateExecuting) = adStateExecuting
DoEvents
Loop
Ocan leave the form and come back and it keeps working. On the multi file imports it's so much nicer because they update a listbox with the status of each file and I intended it to update as it processes each one, but without the DoEvents it would just go white and show the status of all once they were all complete. The difference that a few lines of code can make is pretty amazing!
Go to the top of the page
 
pere_de_chipstic...
post Jul 4 2010, 05:33 AM
Post#10


UtterAccess Editor
Posts: 10,112
Joined: 8-November 07
From: South coast, England


Hi artpolk
leased we could help - Leigh's the man when it comes to the intracacies of handling asynchronous items and ADO .
I had been working on the progress bars and have attached the update - if it might be useful to you.
The main differences are
1. you can 'pause' the process,
2. the third progress bar on the second test shows overall progress rather than a 'cascaded' progress.
3. the progress of the Loop 1 and 2 bars is 'smoothed' for small loop numbers.
4. bars are hidden for 0 loop values
Any comments or suggestions appreciated!
Attached File(s)
Attached File  ProgressMeter.zip ( 30.94K )Number of downloads: 18
 
Go to the top of the page
 
LPurvis
post Jul 4 2010, 07:43 AM
Post#11


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Just waking me up to this again...
That's fine - but you're still relying on the processing windows afforded you by the DoEvents calls.
I'd suggest you, at least, consider the option of using the connection events to determine when the command has completed.
That said, ADO events in Access can seem less than obvious in their sequencing.
If your command is executing nicely asynchronously as you have it implemented currently, then it should do so event driven too.
You'd need to use a class module of course (the form's would do fine) and declare the object appropriately.
The very basics are:
CODE
Private WithEvents mcnn As ADODB.Connection
Sub OpenIt
    'Open the connection
    Set mcnn = New ADODB.Connection
    mcnn.Open strConn
    'Execute command
    .CommandText = "YourProcName"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 120 'or however long is your max
    Set .ActiveConnection = mcnn
    cmd.Execute , , adAsyncExecute
End Sub
Private Sub mcnn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    If adStatus = adStatusOK Then
        Debug.Print "Connection Complete"
        'Advance Progress bar to next command or whatever
    End If
End Sub

Cheers.
Go to the top of the page
 
artpolk
post Jul 7 2010, 03:49 PM
Post#12



Posts: 59
Joined: 31-December 09



Thanks Leigh. I really appreciate all of the help! I'm surprised and pleased on a regular basis to find some of the things that Access can do.
If course I have hit a few limitations, but they're few and far between. Particularly working with subform datasheets and splitforms. I know a lot of people use continuous forms to work around some of the pitfalls, but they come with their own limitations as well - not a true grid, not as intuitive to the user, have width limitations. Ironically it's the stuff you'd think access was designed for where it seems to have weaknesses. VBA lets you do so much that it wasn't designed for, and I see that even traditional programmers are surprised with some of the things we've pulled off in Access.
Go to the top of the page
 
LPurvis
post Jul 10 2010, 01:56 PM
Post#13


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


No worries.
See your other post for my commentary on forms/grids and the rest.
Cheers.
Go to the top of the page
 
artpolk
post Jul 23 2010, 04:21 PM
Post#14



Posts: 59
Joined: 31-December 09



Leigh, isn't there a way to cancel when using Async? I had tried both cmd.Cancel and cmd.ActiveConnection.Cancel
either of them raises an error, but neither seems to cancel the process either. I'm sure it's something simple, but I just haven't figured out what!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 01:41 PM