Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > it.comp.lang.visual-basic > #19931

Dati tabella Excel su tabella SQL Server

Show all headers | View raw

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

Back to it.comp.lang.visual-basic | Previous | Next — Next in thread | Find similar

Thread

Dati tabella Excel su tabella SQL Server Marco75 <marcoporzi75@gmail.com> - 2021-11-05 04:02 -0700
  Re: Dati tabella Excel su tabella SQL Server Marco75 <marcoporzi75@gmail.com> - 2021-11-05 06:12 -0700

csiph-web