DataBase - Compter le nb d'enregistrements d'un recordset
Asked By Isa
08-Sep-09 10:25 AM
Bonjour,
Je souhaiterais compter le nb d'enregistrements d'un recorset.
Pour cela, je voulais utiliser la propri??t?? RecordCount mais je n'arrive pas
?? m'en servir. Je fais un MoveLast avant mais j'ai un erreur sur le MoveLast
("L'ensemble de lignes ne prend pas en charge les r??cup??rations arri??re").
Pouvez-vous m'en expliquer le fonctionnement ou me dire de quelle mani??re
compter ce nb d'enregistrements ?
D'avance merci.
Isa.
CurrentProject.ConnectionSQL
(1)
BonsoirDAO
(1)
RecordCount
(1)
LaTable
(1)
Connexion.Execute
(1)
Pouvez
(1)
Lancer
(1)
Selon
(1)
Thierry replied...
Bonjour,
Je pense que juste apr??s le MoveLast, vous devez faire un MoveFirst
puis utiliser RecordCount.
?? v??rifier...
Bon courage
Michel__D replied...
Bonjour,
Isa a ?crit :
L'autre solution serait de faire une requ?te qui compte, puis de r?cup?rer
le r?sultat, puis de relancer la requ?te initiale, par exemple :
Si la requ?te est SELECT Champ1, Champ2 FROM LaTable;
Lancer la requ?te qui compte => SELECT Count(*) AS Nombre FROM LaTable;
R?cup?rer le r?sultat puis relancer la requ?te initiale, ? savoir SELECT Champ1, Champ2 FROM LaTable;
Isa replied...
J'ai un erreur sur le MoveLast
("L'ensemble de lignes ne prend pas en charge les r??cup??rations arri??re").
Est ce que quelqu'un saurait utiliser la propri??t?? RecordCount ?
D'avance merci.
Isa.
Fabien replied...
Isa a ??crit :
Salut,
Peux tu nous donner la commande par laquelle tu ouvres ton recordset?
Isa replied...
Set connexion = CurrentProject.Connection
SQL = ...
Set R = connexion.Execute(SQL)
Blaise Cacramp replied...
Selon : Bonjour ou bonsoir
DAO ou ADO ?
dans le dernier cas (le meilleur), ouvrir le recordset en "openstatic" et le
recordcount sera exact.
Cdt, Blaise
---- ---- ----
C7DDF22D-9613-4B38-AF2C-E7EB11C8CB59@microsoft.com...
True End If End Function Access Forms Coding Discussions David W. Fenton (1) Macros (1) RecordCount (1) Database (1) Report (1) Seek (1) BOF (1) EOF (1) you cannot use the Best Data Processing - Holiday, FL ng, f A more reliable test if whether or not .Recordcount = 0, and it is only one property that needs to be tested. - - David W. Fenton Further, I cannot recall (does not mean it does not exist to be sure) that RecordCount is ever (exactly) accurate without using MoveLast first. . . While you are correct that RecordCount is not accurate without using MoveLast first, if RecordCount reports 0, then there is nothing in the recordset. Further, I cannot recall (does not mean it does not exist to be sure) that RecordCount is ever (exactly) accurate without using MoveLast first. . . In my experience, .Recordcount will be 0 for an empty recordset; if the recordset returns any records, it will it will be 1 initially, i.e. if you open the recordset and check its recordcount in the next line; the recordcount will increase as the query engine traverses the recordset
TableDeleteRecords(strTableName As String) Dim rst As Recordset Set rst = CurrentDb.OpenRecordset(strTableName) If rst.RecordCount = 0 Then Exit Sub rst.MoveFirst Do While Not (rst.EOF) rst.Delete rst.MoveNext Loop rst.Close End Sub when you pass any file to this rst.recordcount is always equal to 153. I even passed it a table with nothing in it 2010 (1) Windows 7 (1) Error (1) Bit (1) David W Fenton (1) MoveNext (1) RecordCount (1) I thought one always needed to rst.movelast rst.movefirst first to get the correct recordcount. . Only if using a dynamic or keyset cursor. If using a static cursor, then the known when the recordset is populated. The thing is, when using a non-static cursor, RecordCount should contain -1, since the total number of records is not known until the last you need to know at that moment, the number of records in the recordset. rst.RecordCount will be non 0 when a recordset is opened with 1 or more records. It Dim rst As DAO.Recordset Set db = CurrentDB Set rst = db.OpenRecordset(strTableName) If rst.RecordCount <> 0 Then rst.MoveFirst Do While Not (rst.EOF) rst.Delete rst.MoveNext Loop End via website only http: / / www.dfenton.com / DFA / But you do not NEED an accurate recordcount - - you only need to know if the recordset is empty, and in DAO, any non
previous form): Const FormTopMargin = 3D 560 'Const FormScrollBar = 3D 250 'Const FormRecSelectors = 3D 250 Dim RecordCount As Long RecordCount = 3D DCount("*", "tblAlpha") 'Debug.Print (RecordCount) 'form has Header, Detail, and Footer DoCmd.MoveSize , , , (Me.Detail.Height * RecordCount) _ + FormTopMargin In this form, however, there = 92s a subform that also needs to be sized with the RecordCount. I = 92ve tried to do this, but no avail = 85I thought maybe instead of sizing the subform based on the RecordCount, to size the subform relative to the main form. Any ideas? Thanks, alex Access Discussions DoCmd.MoveSize (1) FormRecSelectors (1) FormTopMargin (1) FormScrollBar (1) RecordCount (1) MoveSize (1) TblAlpha (1) Subfrom (1) Remember the subform is a Control, not a form, until you get it is form properties. The RecordCount of the subform can be determined in the Current even of the main form (aircode
Table size or recordcount DataBase Hi I am trying to find the size of each table or if not the db. Is there a script I can run that will table and its size / recordcount? Thanks Regards Access Discussions DBEngine.OpenDatabase (1) CurrentDB.TableDefs (1) DbFrontEnd.TableDefs (1) DbBackEnd.TableDefs Then Set rst = db.OpenRecordset(tdf.name, dbOpenSnapshot) rst.MoveLast Debug.Print tdf.name, rst.RecordCount rst.Close Set rst = Nothing End If Next i Set tdf = Nothing Set db = Nothing access http: / / www.accessmvp.com Or try the following query: SELECT Name, DCount("*", "[" & Name & "]") As RecordCount FROM MSysObjects WHERE Type IN (1, 4, 6) AND Name NOT LIKE "?Sys*" - - Doug Steele if you insist on using a Recordset (instead of just checking the base table's RecordCount property), this would be more efficient: Dim strSQL As String strSQL = "SELECT COUNT(*) FROM " & tdf if DAO is smart enough to short-circuit this and use the underlying TableDef's Recordcount property, but even if it does, it will always take longer to set up the recordset than it would to simply use the Recordcount property directly. - - David W. Fenton http: / / www.dfenton.com / usenet at dfenton dot com http not need to use DCount() or a recordset to find the number of records, as RecordCount is a property of a TableDef that is always kept up-to-date by Jet
Kolele a ?crit : Voici une autre solution en 2 requ?tes et avec une table [LaTable] qui poss?de les champs suivants : [IdEnr], [IdProf], [DateVisite], [Commune] Le champ [DateVisite] doit par T1.DateVisite, "yyyy / mm / dd") AS DateVisite, T1.Commune, Count(T2.IdEnr) AS Ordre FROM LaTable AS T1 INNER JOIN LaTable AS T2 ON (T1.DateVisite> = T2.DateVisite) AND (Format(T1.DateVisite, "dd / mm / yyyy") = Format Date ?] DateTime; SELECT T.IdProf, T.DateVisite, "Origine" AS Depart, T.Commune AS Arrivee FROM LaTable AS T INNER JOIN LaRequete AS R1 ON T.IdEnr = R1.IdEnr WHERE R1.Ordre dd hh:mm:ss") UNION SELECT T.IdProf, T.DateVisite, R2.Commune, R1.Commune FROM LaTable AS T INNER JOIN (LaRequete AS R1 INNER JOIN LaRequete AS R2 ON R1.IdProf SELECT T.IdProf, DateAdd("s", 1, T.DateVisite) AS DateVisite, T.Commune AS Depart, FROM LaTable AS T INNER JOIN LaRequete AS R1 ON T.IdEnr = R1.IdEnr WHERE R1.Ordre 3 requ?tes qui int?gre la distance sachant que tu as les tables suivantes : [LaTable] qui poss?de les champs suivants : [IdEnr], [IdProf], [DateVisite], [Commune] et [LaTable2] qui poss?de T1.DateVisite, "yyyy / mm / dd") AS DateVisite, T1.Commune, Count(T2.IdEnr) AS Ordre FROM LaTable AS T1 INNER JOIN LaTable AS T2 ON (T1.IdProf = T2.IdProf) AND (T1.DateVisite