I recently came across this problem when we moved from Windows 32bit to Windows 64bit, using SQL 2005/2008 64bit. There are a lot of openrowset/opedatasource for older Excel 2003 xls.

I've finally found a free solution. It seems like Microsoft has replacement Office driver for "Microsoft.Jet.4.0" with its "Microsoft.ACE.OLEDB.12.0".

For 32 bits download the 2007 Office System Driver:Data Connectivity Components

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

For 64 bits download the 2010 beta (Release June 2010)

http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=ja&displaylang=en

Installation of both doesn't require reboot. The 32/64 bits odbc will have the ACE driver for Access/Excel/dbase/cvs/txt after installation and the SQL Provider drivers "Microsoft.ACE.OLEDB.12.0" will be in your 32/64bit SQL Engine.

After installation, in SQL Engine, run the following

USE [master]

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

GO

The "Microsoft.ACE.OLEDB.12.0" driver is backward compatible to Excel 2003, so you really can open xls files with this driver.

On a separate note, I've installed Office 2010 beta application, and it did not come with Microsoft.Jet.4.0, so my question is, is Microsoft replacing "Microsoft.Jet.4.0" with "Microsoft.ACE.OLEDB.12.0" on all their future development and slowly phrasing out the jet?