Ciao a tutti... ho un foglio excel con 2 colonne e alcune righe, e vorrei controllare se i dati contenuti in questo foglio siano presenti in una tablella di un DB SQL.
Di seguito il mio codice:
codice:
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset

Sub CheckRecords()

 Set rs = New ADODB.Recordset
 Set oConn = New ADODB.Connection
 oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
 "SERVER=myserver;" & _
 "DATABASE=mydatabase;" & _
 "USER=myuser;" & _
 "PASSWORD=mypassword;" & _
 "Option=3"

 Dim rowtable As Integer 'number of row in the worksheet 
 Dim columntable As Integer 'number of column in the worksheet
 Dim strSQL As String 'SQL string
 Dim stringa As String 'the record in the worksheet that I want to check
 Dim height As Integer 'number of rows with records
  'count the number of rows with records
 height = Worksheets("Distinta").UsedRange.Rows.Count
 For columntable = 1 To 2
  For rowtable = 2 To height
   'variable stringa = the record of my worksheet that I want to check into DB table
   stringa = Worksheets("Distinta").Cells(rowtable, columntable).Value
   strSQL = "SELECT ar_codice FROM `articoli` WHERE ar_codice = '" & stringa & "'"
   rs.Open strSQL, oConn
    If rs.EOF = True Then
     MsgBox "the record " & stringa & " does NOT exist", vbInformation, "Checking"
     Exit Sub
    End If
   rs.Close
   Set rs = Nothing
   oConn.Close
   Set oConn = Nothing
  Next rowtable
 Next columntable
End Sub
C'è un problema con il ciclo For/rs/oConn, perchè se forzo la funzione con un solo valore (senza For insomma), funziona correttamente. Qualche idea?