Provider not specified and there is no designated default provider

I was getting the following error on our new Dell 6850 server with Win2003 64bit and SQL Server 2005 64bit:

"Provider not specified and there is no designated default provider"

Having looked at this page:

http://www.codingforums.com/archive/index.php?t-66468.html (Last post)

It seemed the problem was due to Windows not having a 64 bit version of a database driver. After looking at the advice on the linking page I was still not able to get it going, so I did some experimenting and found the answer.

My old code looked like:

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=MyDSN;UID=MyUser;PWD=MyPassword"
conn.Mode = adModeReadWrite
conn.Open
Set rs = Server.CreateObject("ADODB.Recordset")

Which I changed to:

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB;UserID=MyUser;Password=MyPassword;
Database=MyDatabase"
conn.Mode = adModeReadWrite
conn.Open
Set rs = Server.CreateObject("ADODB.Recordset")

The fix works slightly different and does not rely on a DSN, which for me was no bad thing.