Can you interrupt a data load?

[JWhite]

I have an application where the user can load very large amounts of data into a pivottable. They enter the selection criteria in a menu and I construct the SQL call to either an Oracle or an SQL Server database.

If they're careless with their selection criteria they can run out of memory. The user is soon aware that they've done something wrong because the data loads usually complete within a minute but, if they make a mistake, it may sit there loading data for 20 or 30 minutes before they finally get the error message that they've run out of data.

So they've requested that I give them some way to interrupt the data load if it's taking too long. The only way I know of that they can do that is by killing Excel with the Task Manager. They're not too happy with that option but I don't think I can interrupt the data load once it's started - can I?

FYI, I realize the best solution would be if my program could do a sanity check on their selection criteria before issuing the data load call but since there's a combination of factors involved I usually wouldn't know in advance.

[mdmackillop]

You could set a Flag to False using a timer in your macro and test for this in your loop (assuming there is one), if False then Exit Sub.

[Aflatoon]

Assuming you are using ADO, if you use an asynchronous query, you can begin a transaction, start the query, then cancel it after a set amount of time if it has not finished. You will also need to either alter your code to trap the completion of the query for final processing, or use a loop and check the state. From the ADO Help:
[vba]Public Sub Main()
On Error GoTo ErrorHandler

'recordset and connection variables
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strCmdChange As String
Dim strCmdRestore As String
'record variables
Dim blnChanged As Boolean

' Open a connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn

' Define command strings
strCmdChange = "UPDATE titles SET type = 'self_help' WHERE type = 'psychology'"
strCmdRestore = "UPDATE titles SET type = 'psychology' " & _
"WHERE type = 'self_help'"

' Begin a transaction, then execute a command asynchronously
Cnxn.BeginTrans
Cnxn.Execute strCmdChange, , adAsyncExecute
' do something else for a little while –
' use i = 1 to 32000 to allow completion
Dim i As Integer
For i = 1 To 1000
i = i + i
Debug.Print i
Next i

' If the command has NOT completed, cancel the execute and
' roll back the transaction; otherwise, commit the transaction
If CBool(Cnxn.State And adStateExecuting) Then
Cnxn.Cancel
Cnxn.RollbackTrans
blnChanged = False
MsgBox "Update canceled."
Else
Cnxn.CommitTrans
blnChanged = True
MsgBox "Update complete."
End If

' If the change was made, restore the data
' because this is only a demo
If blnChanged Then
Cnxn.Execute strCmdRestore
MsgBox "Data restored."
End If

' clean up
Cnxn.Close
Set Cnxn = Nothing
Exit Sub

ErrorHandler:
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
[/vba]

[JWhite]

Thanks for the replies. I'll study your suggestions and get back to you. Thanks for the great example, Aflatoon.