How to use excel VBA to SQL query and compare data?

[kickbutt]

I have the below code, which queries a database and compares the recordset against my variable 'StockCode'.

This is not currently working and if i add the line 'Msgbox rs.recordcount' then this comes back with the message '-1'.
However, when i add the line 'ActiveSheet.Ra nge("A5").CopyF romRecordset (rs)' then all records are pasted in to my excel spreadsheet. can someone see what i am doing wrong?

Sub SubmitInterchange()

Dim oConn As Object
Dim sSQL As String
Set rs = New ADODB.Recordset
Dim strSql As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=############;" & _
"Initial Catalog=SysproCompany2;" & _
"User Id=##;" & _
"Password=#######"

strSql = "SELECT StockCode FROM InvMaster"
Set rs.ActiveConnection = oConn
rs.Open strSql

MsgBox rs.RecordCount


'ActiveSheet.Range("A5").CopyFromRecordset (rs)

RowCount = rs.RecordCount
Dim alldata() As Variant
alldata = rs.GetRows(RowCount)

Dim switch As Boolean
Dim StringVal As String

switch = False


For n = 10 To 109

       StockCode = Worksheets("Interchange_Insert").Cells(n, 4).Value
       Barcode = Worksheets("Interchange_Insert").Cells(n, 5).Value

If StockCode > 1 Then

For i = 0 To RowCount - 1

       StringVal = CStr(alldata(0, i))
       StringVal = Trim(StringVal)

    If StringVal = StockCode Then
       switch = True
    End If
    
Next i
            
    If switch = False Then
                MsgBox StockCode & " is an invalid stock code"
                Exit For
    End If
          
switch = False

MsgBox "StockCode:" & StockCode & "  Barcode:" & Barcode & ""
sSQL = "INSERT INTO InvMaster(StockCode, DrawOfficeNum) VALUES ('" & StockCode & "', '" & Barcode & "')"
oConn.Execute sSQL
End If

Next n

oConn.Close
Set oConn = Nothing

End Sub

[MikeTheBike]

Hi

If you add these lines

rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimisti c

before you open the record set then rs.RecordCount will return the correct value. It is good proctice (IMHO) to always specify these perameters.

The other way of forcing the RecordCout property with a Dymamic type record set is

rs.MoveLast

This sets the count propery (don't forget to rs.MoveFirst afterwards!).

HTH

MTH

[kickbutt]

thanks for that Mike!
another line that will need addressing is where i have:
If StringVal = StockCode then
Switch = True
End If

this just does not work, everytime i run the macro it says the stockcode is invalid even though it exists in the table.

thank you

[MikeTheBike]

The only thing I can suggest is you put this line

Msgbox StringVal & " = " & StockCode & " ?"

imediatly before

If StringVal = StockCode Then

and see if you have what you think you have?

Pehaps this may do it

If UCase(Trim(Stri ngVal)) = UCase(Trim(Stoc kCode)) Then

??


MTB

[kickbutt]

hi Mike

I have tried both of your suggestions and the macro is working exactly as it was...
It still displays -1 as the recordcount and does not match the StockCode with StringVal.

I don't think the data is looping as StringVal always returns nothing

thank you

[MikeTheBike]

Hi

Well what can I say, although I haven't connected to an SQL DB, only Access, but this always works for me

Sub test()
    Dim oConn As Object
    Dim rs As New ADODB.Recordset
    Dim strSql As String
    Dim ConStr As String
    Dim alldata() As Variant
    
    Set rs = New ADODB.Recordset
    Set oConn = CreateObject("ADODB.Connection")
    
    ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data "
    ConStr = ConStr & "Source=\\bfpdc\cesaccess$\CESTimesheetsDB\Data\CES Timesheets_BE ST6.mdb;"
    ConStr = ConStr & "Persist Security Info=False"
    
    oConn.Open ConStr

    If oConn.State = adStateOpen Then
        strSql = "SELECT * FROM tblTickets ORDER BY TicketNo"
        With rs
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockReadOnly
            .Source = strSql
            .ActiveConnection = oConn
            .Open
        End With
         
        
        alldata = rs.GetRows()
        
        MsgBox UBound(alldata, 2) & "  :  " & rs.RecordCount
        MsgBox alldata(2, 0)
        
        rs.Close
        oConn.Close
    End If
    Set rs = Nothing
    Set oConn = Nothing
        
End Sub

Again, I have never used the 'rs.GetRows()' method, but you will note that this line

MsgBox UBound(alldata, 2) & "  :  " & rs.RecordCount

displays the number of records and the number -1 (at least in my case), so you can get ther record count from the upper bound property of the Array if all else fails.

There are other ways of doing what (I think) you are doing. I would do it something like this where you don't need to know the number of records by using the recordset EndOfFile property

Sub SubmitInterchange()
  
    Dim oConn As Object
    Dim sSQL As String
    Dim rs As New ADODB.Recordset
    Dim strSql As String
      
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=sqloledb;" & _
    "Data Source=############;" & _
    "Initial Catalog=SysproCompany2;" & _
    "User Id=##;" & _
    "Password=#######"
      
    strSql = "SELECT StockCode FROM InvMaster"
    Set rs.ActiveConnection = oConn
    
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Source = strSql
        .ActiveConnection = oConn
        .Open
    End With
    
    Dim switch As Boolean
    Dim StringVal As String
      
    switch = False
  
    For n = 10 To 109
      
        StockCode = Trim(Worksheets("Interchange_Insert").Cells(n, 4).Value)
        Barcode = Worksheets("Interchange_Insert").Cells(n, 5).Value
      
        If StockCode > 1 Then
            Do Until rs.EOF
                StringVal = CStr(rs(0))
                StringVal = Trim(StringVal)
                If UCase(StringVal) = UCase(StockCode) Then
                   switch = True
                End If
                rs.MoveNext
            Loop
            rs.MoveFirst
            
            If switch = False Then
                MsgBox StockCode & " is an invalid stock code in line " & n & "!"
            Else
                MsgBox "StockCode:" & StockCode & "  Barcode:" & Barcode & ""
                sSQL = "INSERT INTO InvMaster(StockCode, DrawOfficeNum) VALUES ('" & StockCode & "', '" & Barcode & "')"
                oConn.Execute sSQL
            End If
        End If
        switch = False
    Next n
    
    rs.Close
    oConn.Close
    Set oConn = Nothing
  
End Sub

Hope this is of some use, but I do not know why you do not get the correct RecordCount value. Perhaps some one else could enlighten us both?

MTB