Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-09-16
9
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 750 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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