I have been trying the last few days and what I have been doing is googling and trying whatever code I can find and testing out. I give up and hoping the brilliant minds here can help me.
I am trying to load data from an excel worksheet to an already existing table in SQL Server. All the columns and data attributes matches exactly.
When I run the script it gives an error:
Run-time error '-2147217865 (80040e37)':
The Microsoft Access database engine could not find the object 'DATA_FOR_CUTTING'. Make sure the object exists and that you spell its name and the path correctly. If the 'DATA_FOR_CUTTING' is not a local object, check your network connection or contact the server administrator
This is the script (I am using Excel 2010)
Sub Excel_To_SQL()
Dim con As ADODB.Connection
Dim con2 As New ADODB.Connection
Dim s As String
Dim s2 As String
Dim s3 As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\xxx\Documents\xxx\xxxx\Book2.xlsx';" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0;Readonly=False"";"
' 'HDR = yes - to exclude header of excel file upon reading
con.Open s
s2 = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxx;Password=xxx;Initial Catalog=xxx;Data Source=xxxxx;"
con2.Open s2
s3 = "INSERT INTO dbo.DATA_FOR_CUTTING SELECT * FROM [Data_for_Cutting$]"
con.Execute s3
con.Close
Set con = Nothing
Application.StatusBar = "Data successfully loaded."
End Sub
#
I fixed the connection string and there are no more errors, but it also does nothing. Its not reading the contents of the worksheet so no rows are being inserted to the sql table. But if I do the "INSERT INTO ... VALUES (XXX)" command the VALUES are inserted into the SQL table, so the connection and command are working. Anybody know why its not reading the worksheet? Thanks.
Aucun commentaire:
Enregistrer un commentaire