The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages - More
> Visual Basic Programming
|
Returning # of rows in a SELECT statement with VBA
Discuss Returning # of rows in a SELECT statement with VBA in the Visual Basic Programming forum on Dev Shed.
Returning # of rows in a SELECT statement with VBA Visual Basic Programming forum discussing VB specific programming information. Quickly prototype and build applications with this robust and simple language.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
June 4th, 2012, 02:06 PM
|
|
Contributing User
|
|
Join Date: Oct 2006
Posts: 142
Time spent in forums: 11 h 32 m 7 sec
Reputation Power: 7
|
|
|
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
|
June 4th, 2012, 02:16 PM
|
 |
Type Cast Exception
|
|
Join Date: Apr 2004
Location: OAKLAND CA | Adam's Point (Fairyland)
|
|
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
|
June 4th, 2012, 02:55 PM
|
|
Contributing User
|
|
Join Date: Oct 2006
Posts: 142
Time spent in forums: 11 h 32 m 7 sec
Reputation Power: 7
|
|
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
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
