我在运行Excel 2007中的VBA代码时遇到了自动化错误。我正在尝试连接到远程SQL Server数据库,并将数据从Excel加载到SQL Server。
我得到的错误是:
"运行时错误'-2147217843(80040e4d)': 自动化错误"。
我查看了MSDN网站,它建议这可能是由于sqloledb提供程序存在的一个错误,缓解此问题的一种方法是使用ODBC。好吧,我已经将连接字符串更改为反映ODBC提供程序和相关参数,但我仍然得到相同的错误。
这里是使用ODBC作为提供程序的代码:
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Public Sub loadData()
'This was set up using Microsoft ActiveX Data Components version 6.0.
'Create ADODB connection object, open connection and construct the connection string object.
Set cnt = New ADODB.Connection
cnt.ConnectionString = _
"Driver={SQL Server}; Server=onlineSQLServer2010.foo.com; Database=fooDB Uid=logonalready;Pwd='helpmeOB1';"
cnt.Open
On Error GoTo ErrorHandler
'Open Excel and run query to export data to SQL Server.
strSQL = "SELECT * INTO SalesOrders FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', & _
"'Data Source=C:\Database.xlsx; Extended Properties=Excel 12.0')...[SalesOrders$]"
cnt.Execute (strSQL)
'Error handling.
ErrorExit:
'Reclaim memory from the connection objects
Set rst = Nothing
Set cnt = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
Resume ErrorExit
'clean up and reclaim memory resources.
cnt.Close
If CBool(cnt.State And adStateOpen) Then
Set rst = Nothing
Set cnt = Nothing
End If
End Sub