Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MsysQuery (A2000)

    I found the following query which counts the number of records in a database.
    Very useful for checking which tables or linked tables, are used regularly or redundadant.
    I thought I would share it.

    SELECT MsysObjects.Name, DCount("*",[Name]) AS TableCount
    FROM MsysObjects
    WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=6))
    ORDER BY MsysObjects.Name;

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MsysQuery (A2000)

    And how would that be useful? In a relational schema, where related pieces of information for a single *virtual* record are in multiple tables, I don't see how a count will tell you much that you need to know.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsysQuery (A2000)

    I found it useful, (Relational) to double check that records are inserted into the correct tables without opening each table in turn.

    I regularly delete all records from all tables whilst designing.
    I enter dummy information and check it's populating the tables I want to.

    Just one of those useful things, others might want to use for database information !!

    Obviously, if its of no interest to certain members, then skip the post.

    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MsysQuery (A2000)

    You can find out whether the records have been deleted or inserted if you use a querydef object and execute it in code. The querydef object has a RecordsAffected property that will tell you how many records were appended, updated or deleted right then.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsysQuery (A2000)

    Thanks for the info Charlotte.
    <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •