Solved

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

Posted on 2004-09-16
9
359 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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