3Types

Asynchronous connection from Excel to SQL Server - How-to

Posted by 3Types on December 27th, 2008 — Posted in Programming

  Tags: Excel, howto, SQLServer

Many of my programs involve pulling data from our data warehouse, manipulating it a bit, and presenting the results to the user. Because it can time to get data out of the data warehouse (anywhere from a few seconds to 30 minutes for more complicated queries), the program can easily look like its frozen while the user is waiting. In the case of Excel macros, a running query can freeze up not only Excel, but make the whole computer seem sluggish.

This problem can be greatly alleviated by creating an asynchronous connection to the server. In brief, with a normal (synchronous) connection, the program will call a stored procedure or execute a SQL statement on the server and then wait for the results to come back. While it’s waiting, nothing else can be done with the program - or with the calling program (Excel or Access, for example).

An asynchronous connection, on the other hand, basically sends off the SQL statement and says “I’m going to do some other things now, let me know when the results are ready and I’ll pick them up.” By taking the control back right away, the local program can give the user some simple feedback (for example, a status message saying the program has been running for x minutes); more importantly, the user can more easily switch back and forth to other programs, without the sluggishness that comes from waiting for a synchronous program to finish.

I figured out how to create asynchronous connections from Access a few years ago; it’s a bit complicated, but not too bad, because Access forms seem to be tailor-made to handle them. But my attempts to do the same thing from Excel macros kept leading to dead-ends - until I finally had a breakthrough a few months ago and figured out how to make it work!

Following is a step-by-step description of how to set up an asynchronous connection from an Excel macro. I know it works in Excel 2007 connecting to SQL Server 2005; I’m pretty sure it will also work with Excel 2003 and SQL Server 2000, but I haven’t tested them.

There are three key components to an asynchronous connection:

1. A user form that creates an ADODB connection and command, and launches the SQL query.
2. An “ExecuteComplete” routine (in the form module) that will run when the command is finished and the recordset has been returned.
3. A timer that lets the program retain control while waiting for the command to be finished.

Here’s how I set these components up.

In the declarations section of the user form, declare an ADODB connection and recordset, using the “withevents” keyword:

	dim withevents cn as ADODB.Connection
	dim withevents rs as ADODB.Recordset

Also declare any variables that need to be shared between the Run routine and the ExecuteComplete routine.

In the Run routine

(Presumably this is in the “OnClick” event for the Run button.)

Set the connection to a new connection (”set cn = new adodb.connection”), specify the connection string and properties, and open it.

Create the command and parameters.

Before executing the command, start the timer (see below for details).

	StartTimer "Macro1"
	intTimers = intTimers + 1
	dtStartMacro1 = Now

Then open the recordset with this syntax:

	set rs = new adodb.recordset
	rs.open Source:=cmd, Option:=adAsyncExecute
	DoEvents

That’s the end of the Run procedure.

In the ExecuteComplete routine

(NOTE: As far as I can tell, this is the ONLY significant event that’s fired. I can’t get cn_InfoMessage to fire, or rs_FetchComplete or rs_FetchProgress.)

First, stop the timer (see below for details).

	intTimers = intTimers - 1
	StopTimer "Macro1"

Then check the adStatus parameter. If adStatus = adStatusOK, then go ahead and process the results. If not, then use the pError parameter to return the error message to the user.

The timer

This is the part of the process that eluded me for so long! Access forms have a built-in timer property that is much more intuitive than the way Excel does it . . .

These need to be in a regular module, NOT a form or other class module!

Declare these global variables:

	Global intTimers As Integer

(this lets you know how many timers are currently running)

	Global dtStart<proc> As Date

(one of these for each macro that may use the timer)

Declare these variables at the top (so they have module scope):

	Const intInterval As Integer = 10
	Dim dblTime As Double

Create these Public Subs:

NOTE: timers must be started and stopped with EXACTLY the same parameters; hence the need to store the start time in a variable with modular scope! See the Excel help file for more details on the OnTime method.

	Public Sub StartTimer(strMacro As String)
		Select Case strMacro
		Case "Macro1"
			dblTime = Now + TimeSerial(0, 0, intInterval)
			Application.OnTime EarliestTime:=dblTime _
					, Procedure:="UpdateForm_Macro1", Schedule:=True
			DoEvents
		End Select
	End Sub

	Public Sub StopTimer(strMacro As String)
		On Error Resume Next
		Select Case strMacro
		Case "Macro1"
			Application.OnTime EarliestTime:=dblTime _
					, Procedure:="UpdateForm_Macro1", Schedule:=False
		End Select
	End Sub

Then for each macro, create an UpdateForm_<Macro> sub. This updates the userform (and does any other desired processing), and re-calls the timer (the “SecondsToTime()” function is a small function I wrote to translate seconds into a more meaningful time string; the “lblStatus” control is a label on the userform that provides status messages):

	Public Sub UpdateForm_Macro1()
		With frmMacro1.Controls("lblStatus")
			.Caption = "Waiting for the server for " _
				 & SecondsToTime(DateDiff("s", dtStartMacro1, Now))
			StartTimer "Macro1"
	End With

	End Sub

Allowing a graceful exit

One of the best things about an asynchronous connection is that you can allow a way for the user to bail out of the macro if it’s taking too long. With a synchronous connection, the only way to cut it short is to forcibly close Excel via Task Manager (and obviously that’s not the ideal way to close Excel!).

To enable a graceful exit, add a variable bRunning (boolean) to the form declaration section, and set the variable to true just before kicking off the SQL command in the Run command.

Add this routine to the userform:

	Function OKtoClose() As Boolean
		OKtoClose = True
		On Error Resume Next

		If bRunning Then
			If MsgBox("Are you sure you want to stop running this macro?" _
					, vbDefaultButton2 + vbYesNo) = vbYes Then
				StopTimer "Macro1"
				cn.Cancel
				Set cn = Nothing
				OKtoClose = True
		Else
			OKtoClose = False
		End If
	Else
		OKtoClose = True
	End If
End Function

Then, in the userform’s _QueryClose event, add this pre:

	Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
		If Not OKtoClose Then
			Cancel = 1
		End If
End Sub