How to obtain record count

I'd do it in SQL. Use COUNT.

SELECT COUNT (*) FROM table WHERE criteria

Actually, have a look at this page: http://www.techonthenet.com/sql/count.php
It describes it quite well.

How would you assign the value of the sql to a variable (what si the syntax)?

Have a look at this: http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial
'Sample Application 2' is what you want to look at.

Here is my code: (it returns a value of zero when it should be 2)

Dim rst1 As New ADODB.Recordset
Dim NumberFilesToBeReviewed As Integer
Dim strSQL As String
 
    strSQL = "SELECT COUNT(*) as NumberFilesToBeReviewed From tblComparisons WHERE SourceSubFolderPath = SourceFilePath;"

' SourceSubFolderPath  & SourceFilePath are fields within tblComparisons

    rst1.Open strSQL, con, adOpenForwardOnly, , adCmdText

    MsgBox NumberFilesToBeReviewed

You need a recordset.

The code attached is from: http://www.vbexplorer.com/VBExplorer/wrox/sample1061.asp

I believe it explains it better than I can do in words.

Dim myConnection As ADODB.Connection

Dim myRecordSet As ADODB.Recordset

Set myConnection = New ADODB.Connection

 

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\BegDB\Biblio.mdb"

myConnection.Open

' Create a Recordset by executing a SQL statement

Set myRecordSet = myConnection.Execute("Select * From Titles")

' Show the first title in the recordset.

MsgBox myRecordSet("Title")

' Close the recordset and connection.

myRecordSet.Close

myConnection.Close

Select allOpen in new window

Still not working. I htink it is a problem with the where clause, just a hunch...

Dim rst1 As New ADODB.Recordset
Dim NumberFilesToBeReviewed As Integer
Dim strSQL As String

    strSQL = "SELECT COUNT(*) as NumberFilesToBeReviewed From tblComparisons WHERE SourceSubFolderPath = SourceFilePath;"

Set rst1 = con.Execute(strSQL)
 
    MsgBox NumberFilesToBeReviewed
   
    rst1.Close