brontes1
asked on
Recordcount property to retrieve the record count in a table.... Thanks experts
I am currently running vb code in an access database module. I have a funtion the calls a sub within my code. I would like to count the number of records and make sure the total is greater then 25,000. However, I cant even get the recordcount property to work Here is my code I know this code is really waked... I dont think I need a connection string just the set dbs = CurrentDb... not sure... I get snagged at Set rst = dbs.OpenRecordset(SQL)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbs As Database
Dim Sql As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
rst.CursorLocation = adUseClient
Sql = "Select * From [SDS]"
Set rst = dbs.OpenRecordset(Sql)
Debug.Print rst.RecordCount ' Returns the correct number of records
rst.Close
End Sub
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbs As Database
Dim Sql As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
rst.CursorLocation = adUseClient
Sql = "Select * From [SDS]"
Set rst = dbs.OpenRecordset(Sql)
Debug.Print rst.RecordCount ' Returns the correct number of records
rst.Close
End Sub
You're mixing DAO and ADO syntax. That's why your code is failing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you're only interested in the count of records, you could even do this:Dim cnn As ADODB.Connection
Dim Sql As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
Sql = "Select Count(*) AS Records From [SDS]"
rst.Open sql, cnn
Debug.Print rst!Records ' Returns the correct number of records
rst.Close
Dim Sql As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
Sql = "Select Count(*) AS Records From [SDS]"
rst.Open sql, cnn
Debug.Print rst!Records ' Returns the correct number of records
rst.Close
You could always do something as simple as this to just get the record count:
Debug.Print DCount("*","[SDS]")
or
If DCount("*","[SDS]") > 25000 then
...
else
...
end if
Debug.Print DCount("*","[SDS]")
or
If DCount("*","[SDS]") > 25000 then
...
else
...
end if
I also omitted: you'll need to set the connection property by using
cnn.ConnectionString = currentproject.connection
somewhere in the code before opening the recordset. My bad.
cnn.ConnectionString = currentproject.connection
somewhere in the code before opening the recordset. My bad.
ASKER
Do I need a connection string if I have this in my code ... set dbs - currentdb() then Set rst = dbs.OpenRecordset(Sql) ????
My access 2002 gives me an error code on rst.CursorLocation = adUseClient... doent recognize..
Do I need to set a reference to use this property??
Sub RecordCountX()
Dim cnn As ADODB.Connection
Dim Sql As String
Dim dbs As Database
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
rst.CursorLocation = adUseClient
Sql = "Select * From [SDS]"
rst.Open Sql, cnn
Debug.Print rst.RecordCount ' Returns the correct number of records
rst.Close
My access 2002 gives me an error code on rst.CursorLocation = adUseClient... doent recognize..
Do I need to set a reference to use this property??
Sub RecordCountX()
Dim cnn As ADODB.Connection
Dim Sql As String
Dim dbs As Database
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
rst.CursorLocation = adUseClient
Sql = "Select * From [SDS]"
rst.Open Sql, cnn
Debug.Print rst.RecordCount ' Returns the correct number of records
rst.Close
ASKER
I changed the code to
Sub RecordCountX()
Dim cnn As ADODB.Connection
Dim Sql As String
Dim dbs As Database
Dim rst As Recordset
Set cnn = New ADODB.Connection
'Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
Sql = "Select * From [SDS]"
Set rst = dbs.OpenRecordset(Sql)
Debug.Print DCount("*", "[SDS]")
end sub
This runs through without error but no result occurs,,, I cant see a any type of count... when I F8 or step through after the Debug.Print DCount("*", "[SDS]")
I place my mouse cursor on DCount and Debug,Print... I dont get anything... So if it is working where is my count displayed?? Thanks
Sub RecordCountX()
Dim cnn As ADODB.Connection
Dim Sql As String
Dim dbs As Database
Dim rst As Recordset
Set cnn = New ADODB.Connection
'Set rst = New ADODB.Recordset
Set dbs = CurrentDb()
Sql = "Select * From [SDS]"
Set rst = dbs.OpenRecordset(Sql)
Debug.Print DCount("*", "[SDS]")
end sub
This runs through without error but no result occurs,,, I cant see a any type of count... when I F8 or step through after the Debug.Print DCount("*", "[SDS]")
I place my mouse cursor on DCount and Debug,Print... I dont get anything... So if it is working where is my count displayed?? Thanks
Well, you need to pick either DAO or ADO. I'd do the code like this:
'***DAO
Dim dbDAO As DAO.database
Dim rstDAO As DAO.Recordset
Dim sqlDAO As String
Set dbDAO = CurrentDb()
sqlDAO = "SELECT * FROM [SDS]"
Set rstDAO = dbDAO.OpenRecordset(sqlDAO
rstDAO.MoveLast
rstDAO.MoveFirst
Debug.Print rstDAO.RecordCount
rstDAO.Close
Set rstDAO = Nothing
'***ADO
Dim rstADO As ADODB.Recordset
Dim sqlADO As String
sqlADO = "SELECT * FROM [SDS]"
Set rstADO = New ADODB.Recordset
rstADO.CursorLocation = adUseClient
rstADO.Open sqlADO, CurrentProject.Connection
Debug.Print rstADO.RecordCount
Set rstADO = Nothing
Both of these will do what you wish.
'***DAO
Dim dbDAO As DAO.database
Dim rstDAO As DAO.Recordset
Dim sqlDAO As String
Set dbDAO = CurrentDb()
sqlDAO = "SELECT * FROM [SDS]"
Set rstDAO = dbDAO.OpenRecordset(sqlDAO
rstDAO.MoveLast
rstDAO.MoveFirst
Debug.Print rstDAO.RecordCount
rstDAO.Close
Set rstDAO = Nothing
'***ADO
Dim rstADO As ADODB.Recordset
Dim sqlADO As String
sqlADO = "SELECT * FROM [SDS]"
Set rstADO = New ADODB.Recordset
rstADO.CursorLocation = adUseClient
rstADO.Open sqlADO, CurrentProject.Connection
Debug.Print rstADO.RecordCount
Set rstADO = Nothing
Both of these will do what you wish.
But if have to ask: are you just after the count, or when you determine the count do you actually have to do something with the records one at a time? If you're just after the count, then Mourdekai's solution is much more elegant. You don't need any of the ADO or DAO code, just a few lines:
Public Sub CountRecs()
Debug.Print DCount("*","[SDS]")
End Sub
Public Sub CountRecs()
Debug.Print DCount("*","[SDS]")
End Sub