Solved

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

Posted on 2004-09-16
9
353 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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 Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now