When you install Delphi 2010, put it in a virtual machine

I recently compared the performance of virtual machines with the real hardware. The figures were suprisingly good.

Shortly after that, disaster struck. My laptop wouldn't turn on. It took Dell 2 weeks to fix, replacing the motherboard and the video card.

If I had delphi installed directly, I would have had 2 weeks of very limited productivity. As it was, I ripped the hard drive out, stuck it in a usb caddy and continued working on another machine. I was back up to speed the next day.
Edit: It didn't take a full day to get productive again, I did other work until it became apparent that it would be a while before my computer was fixed. It only took about 30 minutes to transfer the data over and get going.

If it had been a hard drive problem, I would have restored the latest vm backup off a dvd, pulled the latest changes from the version control or the source backup and been back up to speed with limited data loss.

I get a new laptop next month. Installing delphi is going to be as easy as installing vmware and copying the vm files over. 30 minutes work, most of which is surfing the internet waiting for the files to copy. The last time I actually had to install Delphi, it took hours.

There are other advantages as well, my development backups fit on a single dual layer dvd, I get to run and test on multiple OSs and disaster recovery plan is much shorter.

So make Delphi 2010 your starting point. Download Virtual Box, VMWare Workstation ($$$) or Virtual PC and use that for development. If you are running Windows 7, you could look into Windows XP Mode and use that.

Changing Excel query connection strings

We use Excel and database queries extensively for reporting purposes. It's quick and easy to set-up, and provides reports that our clients can manipulate.

However I have recently run into a rather painful excel quirk with ODBC connections: Excel stores the database connection string internally. Even if you change the ODBC connection on the computer, excel still uses the original connection from when the query was created.

This bit us when moving the reports to a different machine. Despite having the same ODBC connection set up, excel wouldn't refresh the query and gave the error "[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

Aside
A similar problem can happen if you update the database from sql server 2000 to sql server 2005 or 2008. In that case you may get the error "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON." In that case, you need to alter the connection string to change "APP=Microsoft® Query" to something else. Apparently MS hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason. I suggest "APP=WTFWYT"


Changing one query
(from here)
"Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the following in front of the string:
ActiveCell.QueryTable.Connection =
"

Changing multiple queries in a spreadsheet
(modified from here)
You need to create the following macro (change connection string to suit, see above) and run it in each spreadsheet requiring change. (See the steps below). Once the macro has run successfully, delete it before saving the spreadsheet.

Sub ChangeConnections()
Dim sh As Worksheet, qy As QueryTable
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = "..."
On Error Resume Next
qy.Refresh
If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
Next qy
Next ws
End Sub

Getting the new connection string
The easiest way to find the new connections string is to create a new query and do the ?ActiveCell.QueryTable.Connection trick.