here i used the SQLOLEDB driver name for SQL Server 2000 and it worked absolutely fine. so ithink u too can use the same and below is the form for it
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Provider = "SQLOLEDB"
dbConn.ConnectionString= "SERVER=server1; Database=databasename; UID=xxxx;PWD=yyyy"
Set RecordSet = CreateObject("ADODB.Recordset")
RecordSet.CursorType = 1
sql="select * from Table"
RecordSet.Open sql, dbConn
'Write your logic here
'create params for datasheet, also currRow variable for moving cursor through data table.
currRow = 1
DataTable.LocalSheet.AddParameter "Column Name", ""
'move to first record of recordset
'write to datatable until you can't write anymore. The RS.Fields.Item property is straight ADO, getting recordset contents. The MoveNext moves to the next recordset data.
while not RecordSet.EOF
DataTable.Value ("Column Name", "Action1") = RecordSet.Fields.Item("Column Name").Value