vba使用ADO將EXCEL資料導入到SQL Server

9100bo

Sub test()
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=xxx;" & _
        "Initial Catalog=Mydata;User ID=sa;Password=54123"

       'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO Newtable FROM " & _
        "OPENROWSET('Microsoft.ACE.OLEDB.12.0', " & _
        "'Excel 12.0;Database=C:\Users\My\Desktop\222.xlsx', " & _
        "[Sheet1$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
End Sub

下載 (6.57 KB)

2017-6-3 16:12

使用sql server連結伺服器Excellink
Select * From Openquery(Excellink,'Select * from [sheet1$]')
可以正確導入Excel數據到Sql Server

然而用VBA ADO導入 就會報錯,請問是否是權限問題?