Excel 2007 to SQL Server???

  • Thread starter Thread starter K. Wilder
  • Start date Start date
K

K. Wilder

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
 
I think it means the destination is read-only or non-updateable recordset.
See if this helps. Don't forget to; Tools|References and check the box for
'Microsoft ActiveX Data Objects 2.x Library'

Use the highest version that will still support your clients.


Public Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1


'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"


'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!LastName
Sheets("Sheet1").Range("B" & i) = rs1!FirstName
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub


Public Sub testwrite()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1


'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"


'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenDynamic, adLockOptimistic
For i = 1 To 3
rs1.AddNew
rs1!LastName = Sheets("Sheet1").Range("A" & i)
rs1!FirstName = Sheets("Sheet1").Range("B" & i)
i = i + 1
rs1.Update
Next
rs1.Close
cnn.Close
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

K. Wilder said:
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
Click to expand...
 
Back
Top