Excel bulk data copy to SQL server using VBA / ADO

[kaggel]

I am fairly familiar with recordset basics and have used Excel / VBA to interface with a back-end database in the past. I have a problem where data contained in an Excel spreadsheet is taking too long to update to a SQL server due to the many rows of data (recordset.update or updatebatch is taking forever). I am trying to use some sort of bulk data insert statement to inserts all rows contained in a spreadsheet into a database table. I have used a couple of variations to attempt this. I am however not able to get this to work. I am currently getting a long run time error at the “cn.execute” statement along the lines of: “The OLE DB provider … for linked server (null) reported an error. The provider did not provide any information about the error.” What is the best method to achieve this? Apparently the "Openrowset" method will also require some special server side settings, which I am trying to get away from.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am using Excel 2007 and SQL server 2005. My code currently looks as follows:
<o></o>

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
 
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=localhost;" & _
"Initial Catalog=TestDBName;User ID=sa;Password=password "
<o:p></o:p>
'Import by using OPENROWSET and SELECT query.
strSQL = "SELECT * INTO TableName FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=" & ActiveWorkbook.FullName & "', " & _
"'SELECT * FROM [Sheet1$]')"
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
<o:p></o:p>
cn.Close
Set cn = Nothing
End Sub