VBA MSSQL’e veri yazma

Sub sqlaktarim()
 
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnectionString As String
Dim strInsert As String
Dim ws As Worksheet
Dim son As Integer
Dim sonn As Integer
Dim kntrl As Boolean
 
Set ws = Sheets("DEPO")
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection
son = ws.Cells(Rows.Count, 1).End(xlUp).Row
 
strConnectionString = "Provider=SQLOLEDB;Data Source=...........\SQLEXPRESS;Initial Catalog=uretim;User ID=sa;Password=............. ;"
 
For j = 2 To son
 
On Error GoTo ErrorHandler
cnn.ConnectionTimeout = 1
cnn.CommandTimeout = 0
cnn.Open strConnectionString
rs.Open "SELECT distinct ProfNo FROM [uretim].[dbo].[ProformaVerileri] where ProfNo='" & ws.Range("j" & j) & "'", cnn
kntrl = rs.BOF
 
If kntrl = True Then
cnn.Close
sonn = WorksheetFunction.CountIf(ws.Range("j:j"), ws.Range("j" & j))
For i = j To sonn + j - 1
 
cnn.Open strConnectionString
 
strInsert = "insert into ProformaVerileri ([TeklifTarihi],[Firma],[Banka],[OdemeSekli],[ParaBirimi],[FaturaNo],[TeklifVeren],[TeslimSekli],[ProfNo],[SabitIskonto],[EkIskonto],[Tip]" & _
",[Izo],[Cap],[Miktar],[Boy],[Koli],[AdımBR],[AdımCK],[Iskonto],[OzelFiyat],[TL$€/m],[TL$€/kutu],[TL$€],[TL$€/kutuG],[TL$€G],[Nakliye],[Masraf],[SonIskonto]" & _
",[Hacim],[SatisIndeks],[TL$€/kg],[SatisIndeksTotal],[TL$€/kgTotal])" & _
"values ('" & WorksheetFunction.Text(ws.Range("B" & i).Value, "yyyy-mm-dd HH:dd:ss") & "'," & _
"'" & ws.Range("c" & i).Value & "'," & "'" & ws.Range("d" & i).Value & "'," & "'" & ws.Range("e" & i).Value & "'," & _
"'" & ws.Range("f" & i).Value & "'," & "'" & ws.Range("g" & i).Value & "'," & "'" & ws.Range("h" & i).Value & "'," & _
"'" & Replace(ws.Range("ı" & i).Value, ",", "") & "'," & "'" & Replace(ws.Range("j" & i).Value, ",", ".") & "'," & "'" & Replace(ws.Range("k" & i).Value, ",", ".") & "'," & _
"'" & ws.Range("l" & i).Value & "'," & "'" & ws.Range("m" & i).Value & "'," & "'" & ws.Range("n" & i).Value & "'," & _
"'" & ws.Range("o" & i).Value & "'," & "'" & ws.Range("p" & i).Value & "'," & "'" & ws.Range("q" & i).Value & "'," & _
"'" & ws.Range("r" & i).Value & "'," & "'" & ws.Range("s" & i).Value & "'," & "'" & ws.Range("t" & i).Value & "'," & _
"'" & ws.Range("u" & i).Value & "'," & "'" & ws.Range("v" & i).Value & "'," & "'" & ws.Range("w" & i).Value & "'," & _
"'" & ws.Range("x" & i).Value & "'," & "'" & ws.Range("aa" & i).Value & "'," & "'" & ws.Range("ab" & i).Value & "'," & _
"'" & ws.Range("ac" & i).Value & "'," & "'" & ws.Range("ad" & i).Value & "'," & Replace(ws.Range("ae" & i).Value, ",", ".") & "," & _
"'" & ws.Range("af" & i).Value & "'," & "'" & ws.Range("ag" & i).Value & "'," & "'" & ws.Range("ah" & i).Value & "'," & _
"'" & Replace(ws.Range("aı" & i).Value, ",", ".") & "'," & "'" & Replace(ws.Range("aj" & i).Value, ",", ".") & "'," & "'" & ws.Range("ak" & i).Value & "')"
 
cnn.Execute (strInsert)
 
If cnn.State = 1 Then
cnn.Close
End If
 
Next
 
End If
 
If cnn.State = 1 Then
cnn.Close
End If
 
Next
 
ErrorHandler:
Exit Sub
 
End Sub