I thought Excel 2007 had built-in access to work with SQL Server and to write
C# code, but I guess not.

I'm trying to send data from my Excel 2007 worksheet to SQL Server 2000 or
2005 and I'm not able to get it to work correctly.

I can write VBA code that can create a temp table in SQL Server to send the
data to, but that does me no good. When I try to send the data directly to
the Products table (where the data belongs), I get errors and they are
probably data type errors, but it doesn't say.

Here's the error:

-2147467259 - ODBC call failed

Does anyone have a working solution to get data from Excel 2007 to SQL Server?

Thanks,

KWilder


My code:

Sub ExportData()
On Error GoTo errorHandler

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\stuff\BusinessCardMV1b.xlsx;" & _
"Extended Properties=Excel 12.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=localhost;Database=idcprintingdb;" & _
"UID=idcdbadmin;PWD=$3Zww5N$].Products " & _
"FROM [Sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing


errorHandler:
MsgBox Err.Description


End Sub