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.Range("A5").CopyFromRecordset (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 = adLockOptimistic

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(StringVal)) = UCase(Trim(StockCode)) 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