Solved

Recordcount property to retrieve the record count in a table.... Thanks experts

Posted on 2004-09-16
9
360 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:brontes1
  • 6
  • 2
9 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12077884
You're mixing DAO and ADO syntax.  That's why your code is failing.

0
 
LVL 12

Accepted Solution

by:
pique_tech earned 250 total points
ID: 12077900
Try 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 * From [SDS]"
rst.Open sql, cnn
Debug.Print rst.RecordCount ' Returns the correct number of records
rst.Close
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12077926
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
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 8

Expert Comment

by:Mourdekai
ID: 12077930
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
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12078002
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.
0
 

Author Comment

by:brontes1
ID: 12078218
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
0
 

Author Comment

by:brontes1
ID: 12078394
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
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12078452
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.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12078518
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question