#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    144
    Rep Power
    9

    Returning # of rows in a SELECT statement with VBA


    I'm trying to execute a select statement from VBA and want to work with the returned values. Basically I want to test if there are already rows in existence for specific conditions. I think the logic here would be to execute a select query, assign the returned values to a RecordSet, and do a count of the recordset. However, I'm not sure how to do this in VBA. I can execute delete and insert commands, but for commands like select where it returns data, I'm not sure how to do it. Only the VBA part is foreign to me, I know my way around the SQL. Any ideas?
    Cheers,
    Fred Liu
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    SQL is the same as in the native DB environment. You make the connection to whatever (DB2, Oracle, etc) and execute the same query you'd use directly against your database. Including all the date formatting quirks, wild card variances, etc.

    Assign the result to a recordset and you can use .RecordCount on the recordset (if read only) to get the number of records. Or refer to individual fields in the recordset as rs![Field Name] and use .MoveNext, .EOF etc properties of the recordset accordingly.

    Simplified

    Code:
        cCon.Open MyConnectionString
        cRS.Open strSQL, cCon, adOpenStatic, adLockReadOnly
        MsgBox cRS.RecordCount
        MsgBox cRS.Fields(1).Name
        MsgBox cRS.Fields(1).Value
    'etc
    NOTE: Above example is ADO not DAO
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    144
    Rep Power
    9
    Thanks. I've incorporated it into my code, however I am getting a VBA error at runtime saying my recordSetFred.Open line has arguments that are of the wrong type ... The code works fine before, so the DB connections should be right.
    Code:
    Public Sub uploaddata()
        Dim conn As connect
        Set conn = New connect
        
        Dim tableindex() As String
        
        Dim i As Integer
        Dim sqlstr As String
        
        Dim recordSetFred As Recordset
        Set recordSetFred = New Recordset
        
        'connect to DEV
        'conn.connect_devstf
        conn.connect_devstf
        
        'delete existing data
        tableindex = getuniqueinventory
    
        For i = 0 To UBound(tableindex)
            ' The order of the tableindex is processDateInt, inventoryID, traderID
            sqlstr = "delete from equity.dbo.EnhanceGamma " + _
                " where ProcessDateInt = " + Split(tableindex(i), ";")(0) + _
                " and inventoryId = '" + Split(tableindex(i), ";")(1) + "'"
            conn.m_rds.Open sqlstr, conn.m_conn
        Next
        
        'insert new data into UploadedExpiry table
        i = 1
        Do While Range("Expiry").Offset(i, 0) <> ""
            sqlstr = "select ticker from equity.dbo.enhancegamma" & _
                " where ticker = '" & Range("Expiry").Offset(i, 1) & "'" & _
                " and exchange = 'ca'" & _
                " and spot = " & Range("Expiry").Offset(i, 5)
                
            recordSetFred.Open sqlstr, conn, adOpenStatic, adLockReadOnly
            MsgBox recordSetFred.RecordCount
                
            With Range("Expiry")
                sqlstr = "INSERT INTO equity.dbo.EnhanceGamma " & vbCrLf & _
                         "VALUES(" & .Offset(i, 0) & ", " & _
                         "      '" & .Offset(i, 1) & "', " & _
                         "      'CA', " & _
                         "      '" & .Offset(i, 2) & "', " & _
                         "       " & .Offset(i, 3) & ", " & _
                         "       " & .Offset(i, 4) & ", " & _
                         "       " & .Offset(i, 5) & ", " & _
                         "       " & .Offset(i, 6) & ", " & _
                         "       " & .Offset(i, 7) & ", " & _
                         "       " & .Offset(i, 8) & ", " & _
                         "      '" & .Offset(i, 9) & "' " & _
                         "       )"
            End With
            conn.m_rds.Open sqlstr, conn.m_conn
            i = i + 1
        Loop
        conn.disconn
        Set conn = Nothing
    End Sub
    Cheers,
    Fred Liu

IMN logo majestic logo threadwatch logo seochat tools logo