counting rows in a table

[mary555]

I have a simple question that I thought I'd know how to do. I have an OnTimer Event on my form that needs to have a loop. This loop will run as many times as rows there are in table A. So i thought i could declare an integer and do
Dim num As Integer
num="Select count(*) from Table A"

when i do that i get type mismatch. can anyone tell me how to fix this.

 

[SkipVought]

Hi,

Dim sSQL As String
sSQL = "Select count(*) from Table A"

you are just assigning a STRING.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mary555]

ok thanks...but then if i need the number that it returns (for example it should return 4 if there are 4 rows), will sSQL have the value 4 or have the value of the exact sql statement?

 

[SkipVought]

That value is returned as one of the properties of a Recordset Object.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mary555]

Ok, so how do I get that integer value as a variable that i will be able to use in my 'for' loop

 

[SkipVought]

You don't necessarily need that value. You could loop thru each record object in the recordset collection, using the MoveNext method, testing for the EOF property of the recordset.

Please post the code that you're using to open the recordset.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mary555]

oh ok i see what you mean.
is this how i would do it...

Dim sSQL As String
sSQL = "Select count(*) as rowcount from Alarms"
Do While Not sSQL.EOF
' my code here
sSQL.MoveNext
Loop

or do i need to do something else with sSQL?
Thanks

 

[SkipVought]

You need to do some research on database objects.

here's a sample...

    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT PM.Nomen "
    sSQL = sSQL & "FROM FPRPTSAR.Part_Master PM "
    sSQL = sSQL & "WHERE PM.PART_ID='" & sPN & "' "
    
'    Sheets("sysParms").Range("SQL_Code").Value = sSQL
        
    With rst
      .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
       .MoveFirst
       GetNomen = rst("NOMEN")

       .Close
    End with
    cnn.Close

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[PHV]

And what about this ?
Dim num As Integer
num = DCount("*", "[Table A]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886