Results 1 to 4 of 4
  1. #1
    Boltsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5

    True Recordcount? More records than visible in the table

    Hi,



    Can anyone clarify why this could be happening? I perform a recordcount on a table using tabledef in VBA and I get a recordcount of 6289. I can physically see in my table 5201.

    If I create an sql string as a recordset on the same table and perform a recordcount I also get 5201.

    Where are the extra records coming from? Do they actually exist?

    Any help would be appreciated.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What code are using?
    Are the tables linked or local?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Boltsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5
    Hi,

    The tables are linked, but I have run the code on the back end (so local as well!)

    The code I have used is for the sql string is: (obviously this is part of the code - I haven't included variable declarations etc...)




    ssql = "SELECT * from tblCust" 'ORDER BY CustID Desc"
    Set rs = db.OpenRecordset(ssql)
    On Error Resume Next 'no records in table
    rs.MoveLast
    On Error GoTo 0
    strBeforeOutput = strBeforeOutput & rs.RecordCount & " records" & vbCrLf



    For the table def:

    'Count records in each table
    For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    Set rs = db.OpenRecordset(tdf.Name)
    On Error Resume Next 'no records in table
    rs.MoveLast
    On Error GoTo 0
    strBeforeOutput = strBeforeOutput & " " & tdf.Name & " - " & rs.RecordCount & " records" & vbCrLf

    End If
    Next

  4. #4
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Strange. The code below works properly on a mixture of SQL server linked tables, and local ones.
    Have you deleted records from the tables concerned but not compacted and repaired / closed and reopened the database?
    Code:
        Dim ssql As String
        Dim strBeforeOutput As String
        Dim db As Database
        Dim rs As Recordset
        Dim tdf As TableDef
        
        Set db = CurrentDb
     
    For Each tdf In db.TableDefs
            'Debug.Print tdf.Name
            If Left(tdf.Name, 4) <> "MSys" Then
                Set rs = db.OpenRecordset(tdf.Name, dbOpenSnapshot)
                'Debug.Print tdf.Name
                If Not rs.EOF And Not rs.BOF Then
    
    
                    rs.MoveLast
    
    
                    Debug.Print tdf.Name & " - " & rs.RecordCount & " records" & vbCrLf
                End If
                Set rs = Nothing
            End If
        Next
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. On Field Change, Visible = True
    By wtucker in forum Access
    Replies: 20
    Last Post: 04-25-2018, 12:11 PM
  2. Replies: 7
    Last Post: 03-31-2018, 04:38 PM
  3. Replies: 4
    Last Post: 09-09-2017, 10:24 PM
  4. Replies: 1
    Last Post: 03-27-2016, 10:29 PM
  5. visible form but not have focus when true
    By Ruegen in forum Programming
    Replies: 5
    Last Post: 04-30-2015, 10:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums