Ciao,
devo popolare una tabella SQL Server a partire da Excel con VBA.
ho copiato un codice usato in un altro file che funziona benissimo ma in questo file non riesco a farlo funzionare!
Ho un errore in Update del recordset (Errore di sistema &H80040E2F
(-2147217873).
questo il codice:
Dim R1 As String
R1 = MsgBox("Sto per inviare i dati al server..." & vbNewLine & vbNewLine & "Confermi?", vbQuestion + vbYesNo, "Salvataggio dati")
If R1 = vbYes Then
Dim conn As ADODB.Connection
Dim ServerName As String
Dim DbName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim RowCounter As Long
Dim ColCounter As Integer
Dim NoOfFields As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim r As Long
Dim cs As String
Dim strSQL As String
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
NoOfFields = 16
StartRow = 2
EndRow = lRow
Set conn = New ADODB.Connection
cs = "DRIVER=SQL SERVER;"
cs = cs & "DATABASE=StoricoTare;"
cs = cs & "SERVER=AAA\SQLEXPRESS"
conn.Open cs, "User", "Password"
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
strSQL = "Select * From T_StoricoTareTest;"
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic
'rs.Open TableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 2
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew
.Fields("DataOraInserimento") = Range("Q" & r).Value
.Fields("Anno") = Range("R" & r).Value
.Fields("Mese") =Range("S" & R).Value
.Fields("PianoIspezione") = Range("A" & r).Value
.Fields("MediaSG") = Range("D" & R).Value
.Fields("M") = Range("E" & R).Value
.Fields("Min") = Range("F" & R).Value
.Fields("Max") = Range("G" & R).Value
.Fields("DevSt") = Range("H" & R).Value
.Fields("3Sigma") = Range("I" & R).Value
.Fields("Scost+") = Range("J" & R).Value
.Fields("Scost-") = Range("K" & R).Value
.Fields("ATB+") = Range("L" & R).Value
.Fields("ATB-") = Range("M" & R).Value
.Fields("PercentATB_Sup_Inf") = Range("N" & R).Value
.Fields("MediaArrotondata") = Range("O" & R).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
MsgBox ("I dati sono stati caricati sul server..."), vbInformation + vbOKOnly, "Salvataggio dati"
ElseIf R1 = vbNo Then
MsgBox ("Salvataggio dati annullato..."), vbInformation + vbOKOnly, "Salvataggio dati"
End If