I am a new SQL user and trying to export an excel file to sql after importing SQL file in excel and then, if I modify that in excel, and again export that file in SQL ;it's appending records to the existing SQL file. But I wanted to replace that file ;unfortunately it's not working.
The following code is working perfectly while exporting to SQL but it's not replacing data. Any help would be highly appreciable.
Dim con As New ADODB.Connection Dim iRowNo As Integer Dim sPeriod, sMERC_ID, sLink_ID As String Dim sql As String
Dim sql As String
'Open a connection to SQL Server ServerName = "10.38.46.74" 'Setting ConnectionString con.ConnectionString = "Provider=SQLOLEDB; " & _ "Data Source=" & ServerName & "; " & _ "Initial Catalog=Dare_Work;" & _ "User ID=au\chowdhsy; Password=; " & _ "Integrated Security=SSPI;" 'Setting provider Name con.Provider = "Microsoft.JET.OLEDB.12.0" 'Opening connection con.Open With ThisWorkbook.Sheets("Export") 'Skip the header row iRowNo = 2 'Loop until empty cell in CustomerId Do Until .Cells(iRowNo, 1) = "" sPeriod = .Cells(iRowNo, 1) sMERC_ID = .Cells(iRowNo, 2) sLink_ID = .Cells(iRowNo, 3) 'Generate and execute sql statement to import the excel rows to SQL Server table sql = "insert into dare_work.daredbo.ExcelDemo (Period, MERC_ID, Link_ID) values ('" & sPeriod & "', '" & sMERC_ID & "', '" & LINK_ID & "')" iRowNo = iRowNo + 1 con.Execute sql Loop MsgBox "Customers exported." con.Close Set con = Nothing End With