How do I find the number of rows in my access database? 1

[treyball3]

I have a program that accesses an access database. I want to be able to know the number of rows/records in the database, because it will be constantly changing. I've tried rst.MoveLast followed by rst.RecordCount, but it always gives me a value of 1. Is this supposed to work, or is there a better way to do it? I have:

Dim gdbCurrent As Database
Dim rstCurrent As Recordset
...
Set gdbCurrent = _
OpenDatabase(App.Path & "\test.mdb&quot
...
rstCurrent.MoveLast
intTemp = rstCurrent.RecordCount
...

Any help would be greatly appreciated.
Thanks

 

[dwmtractor]

Your rstCurrent.Movelast statement tells the database to select only the last record from the database into the recordset, thereby giving you only one record to count, which means the count of 1 is correct.

Instead, try:

Dim Doc strQuery1 As String, dbcount As Integer
Dim conStr1 As String
Dim conObj1 As New ADODB.Connection, rsObj1 As New ADODB.Recordset

conStr1 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<filepath to database>;Mode=ReadWrite;Persist Security Info=False&quot;
strQuery1 = &quot;SELECT * FROM [tablename]&quot;

conObj1.Open (conStr1)
rsObj1.Open strQuery1, conObj1, adOpenStatic, adLockReadOnly

dbcount = rsObj1.RecordCount

This way, your count is being conducted on a recordset thta includes all your records. You can also count records with specific criteria this way by including the text &quot;WHERE <fieldname> = '&quot; & <variablename> & &quot;'&quot;

On this last example be *real* careful with your single & double quotes 'cause if you get them wrong you're hosed!

Cheers,

Dan

 

[snalwala]

or else you can try this running into loop..although it will take more time to run the code then the conventional code.

dim Cnt as long

set rstCurrent = db.openrecordset(&quot;select * from EMP&quot

cnt = 0
do while not rstCurrent.eof
cnt = cnt+1
rstCurrent.movenext
loop

rstcurrent.close

 

[zeevgetner]

another way is to use a query &quot;select count(*) from my_table&quot; and to use it in your code.