It started just before Christmas when we got a Kitchenaid mixer that my wife had wanted for a while. I decided to try it out on bread dough.

During one of our book-buying sprees a couple of years ago, we picked up The Bread Baker’s Apprentice, by Peter Reinhart. I had tried out his technique for pizza dough (in American Pie) with success, so I started to study his bread baking techniques. He’s very big on preparing portions of the dough ahead of time and allowing it to “ferment”, and he also talks a lot about the chemical reactions that go on when preparing and baking the dough; I’m not sure that I follow all of the details, but I got the basic idea and decided to start with his recipe for French bread.

The first day I prepared the portion to be “fermented” - just flour, water, salt, and yeast, mixed in the mixer then kneaded with the dough hook, then stored overnight in the refrigerator. I finished the loaves the next day, with more flour, water, salt, and yeast, mixed with yesterday’s dough, then kneaded with the dough hook. After various periods of resting and rising, I shaped the dough into three long skinny cylinders, preheated the oven (and pizza stone) as hot as it would go, and slid the loaves onto the pizza stone. After about 20 minutes, I had these beautiful baguettes on the cooling rack, and in another hour I was savoring some delicious bread.

Since then I’ve tried whole wheat loaves (pretty good, but I used some rye flour that gave it a slightly “off” taste; I’ll probably try it again with corn meal instead), Viennese bread (very nice sandwich bread), and, my favorite, Pain a l’Ancienne (”bread made in the traditional way”). The pain a l’ancienne is truly amazing - a very crusty, airy baguette with a very subtle sweetness, and it’s made only with flour, water, yeast, and salt.

What’s nice about all of these recipes is that most of the work is done by the mixer, and most of the time involved is just waiting for the dough to rise or ferment or whatever else it’s supposed to do. My actual working time is maybe 15 minutes the first day, followed by another 15 to 20 minutes the next day.

I’m preparing for the new data sources by rewriting my programs to refer to views rather than the underlying tables; I’m hoping that will enable me to just change the views when necessary, rather than re-write the stored procedures.

I took advantage of the quiet time between Christmas and New Years to upgrade one of my earliest projects, a series of sales reports I call “POSFlash”. When I first started this job, I inherited a few old Access 97 reports that told our key national account reps what books their accounts sold in the previous couple of weeks. This point-of-sale (”POS”) data is crucial to our business - reps would review the report every Monday morning to find fast-selling titles that they could get reorders on. But the Access 97 reports gave only limited sales data (limited number of titles and only the most recent weeks of sales), and the reports took a long time to run.

So I converted the reports to run directly on SQL Server with stored procedures and used Access XP merely as a front-end. That is, I use Access to create or modify the report definitions (which customers to include, which titles, what fields, etc.), and once the SQL Server stored procedure generates a weekly report and stores it in a report table, the Access program downloads just the report data, loads it into an Excel spreadsheet, and formats it for the users.

Moving the process over to the new 2005 went quite smoothly. The code in the primary stored procedure is a little under 1,000 lines, but it didn’t require a lot of rewriting. (The biggest change was that I was able to use SQL 2005’s new TOP (@x) feature to remove a few dozen lines of code.) The biggest surprise was when I finished debugging the code and discovered that I was able to generate most of the weekly reports in 30 seconds or so - versus the more typical 30-60 minutes required by the old version! (I’m sure that at least 95% of the speed improvement is because the new server is faster, not because of any of my tweaks . . .)

Once the new program was in place on the new server, I set up a Reporting Services report to run the master procedure at the appropriate intervals (4 or 5 times a day on Monday and Tuesday, less frequently as the week goes on - at times chosen based on when the underlying data is loaded into our data warehouse), and for the week of January 5th I ran the old and new reports in parallel.

There were a few glitches during the week, mostly due to typos or other errors in the snippets of code (stored in tables) that specified where the data for each report was to be found. By the end of the week I had quashed those bugs, and for the week of January 12th we switched over to the new version. As far as I can tell, none of my users noticed the difference, except that possibly the reports were available a little bit sooner than before.

Obviously I’m pleased that the upgrade went well!

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

I got my first publishing job in January, 1979, when I interviewed with the personnel director at G. P. Putnam’s. I had decided that there was no point in continuing with my graduate school studies (the job market was awful, and I was getting pretty tired of academia), so I had this vague notion of becoming an editor.

So I visited an employment agency, which sent me to meet with Dorothy Rudo, Putnam’s personnel director, to interview for one of their perpetually available publicity assistant jobs. Dorothy quickly decided I wasn’t cut out for publicity, but she liked me. I think what clinched a job for me was the typing test I took. She asked me to sit at her typewriter and type a sample, which I did with no problem. I then discovered that this was a brand new electric typewriter that she hadn’t figured out how to turn on yet, so my competence with the fancy new technology landed me a job!

I spent a year and a half working as Dorothy’s assistant and as assistant to the president, doing a little bit of everything. It was a great, practical introductory course in publishing.

When I decided it was time to leave New York, I approached Putnam’s sales manager to see if there were any openings for sales reps. I had no selling experience, but I had worked in a bookstore during college, and I got a shot at a territory in St. Louis (which was used for several years as a “training territory” by many publishers).

I found the job suited me very well, and I enjoyed it a lot. I mean, what’s not to like about driving around, visiting bookstores, and talking to them about new books coming out? Just about all my customers were friendly, and I very much enjoyed working out of my home, on my own schedule.

After a year and a half in St. Louis, I took a territory in Southern California, and (aside from a brief, disastrous year back in NY) I’ve been here ever since.

Meanwhile, the job of a sales rep has changed dramatically since I started. I initially worked for mid-sized publishers and covered territories with 80 - 120 customers; I saw each customer once a season (with two or three selling seasons per year) and each sales call took maybe a half a day, except for the stores where I did a complete inventory of their stock (which typically added another half a day).

I almost never saw customers more than once a season (except for the largest ones in my immediate vicinity), and my only other contact with them was by mail and (occasionally) phone.

A few times each week, I’d gather up all the orders I’d collected (on NCR pads for new title orders and in multi-page booklets for backlist orders) and mail them to the warehouse.

Since then, the number of independent bookstores and local wholesalers has shrunk dramatically, and publishers have consolidated so that there are very few “mid-sized” houses left. Now the average sales rep for a large publisher has perhaps 10 to 20 customers, each customer is seen several times during the season, orders are placed electronically (either through the stores’ own computer system or through the rep’s laptop), and the rep probably spends more time discussing marketing plans than actually selling books.

Instead of selling a list of perhaps 100 new titles and 500 - 1,000 backlist titles, today’s large publisher rep has a list of 700 - 1,000 titles per season and a backlist of 5,000 - 10,000 titles!

And of course communication with accounts is now mostly by email or phone. About the only things that are mailed these days are catalogs - and it looks like within the next year or so even catalogs will be replaced by “e-catalogs” (always up-to-date, with lots of bells and whistles - but harder to circulate among a staff of buyers, and a lot less portable . . .).

Despite the drastic changes, the rep job is still one of the best in the business.

We have several different online systems that will provide information about our titles - but most of them work on one title at a time. So if a rep or a sales assistant needs, say, updated prices for a spreadsheet full of titles, she would need to look up each title individually. But with Sales Macros, the whole spreadsheet can be filled in with a single operation!

It started off with programs that I needed for myself; I think the first one was a routine to check the validity of ISBNs (the industry’s standard book number) and restore the leading 0s that Excel frequently dropped.

My big breakthrough was when I figured out how to write a SQL Server stored procedure that would accept a list of ISBNs as a parameter, query our data warehouse for various bits of data (title, price, on-sale date, sales . . .), and return a recordset back to Excel for loading into the spreadsheet.

The basic structure is the same, then, for a half-dozen key macros. The user is presented with a dialog box that asks for the spreadsheet column containing the ISBNs, and presents a list of available data fields that the macro will return; the user chooses which fields he wants and which column in the worksheet will hold the data. (The program tries to guess at the appropriate columns, based on the headers, but everything can be changed or corrected easily.)

Once the user makes his selections and clicks the “Run” button, Excel assembles a list of the ISBNs (in the format “Row#,ISBN|Row#,ISBN . . .”) and passes that as a parameter to the appropriate stored procedure. The procedure then breaks apart the ISBN parameter into a temporary table and uses that table to pull the desired data from the data warehouse. Once all the data has been assembled, the stored procedure returns the data in the form of a recordset to the Excel macro.

Once Excel receives the recordset, it loads the worksheet using the row number and the user-selected column assignments. In most cases, the whole process takes a minute or two (up to 10 minutes or more if the spreadsheet has thousands of ISBNs) - and that’s obviously a huge time savings over looking up the data on a title-by-title basis!