Query MSysObjects

[MadTom]

I have this query to list tables in my DB, I would like to add a column that shows the number of records for each table.
I can do this for one table, but not with MSysObjects.


Thanks!

SELECT MSysObjects.nameFROM MSysObjects
WHERE MSysObjects.type In (1,)
   and MSysObjects.name not like '~*'      
   and MSysObjects.name  like 'tblPartsB*'
ORDER BY MSysObjects.name;

[ranman256]

stay out of the MSysObjects.
Theres nothin you need there.

dim db as database
dim tdf as tabledeff


Set db = CurrentDb
For Each tdf In db.TableDefs
   If InStr(tdf.name, "MsSys") = 0 and left(tdf.name,1) <> "~" then
       debug.print  tdf.name, dcount("*", tdf.name)
   endif
Next
MsgBox "Done"


Set tdf = Nothing
Set db = Nothing

[orange]

I agree with ranman256 --Do not play with/in MSysObjects.

[MadTom]

I can't get your code to work the way I needed, I had to change "dim tdf as tabledeff" , runs but doesn't show count.
I know you don't like MSysObjects, but I got my query to work the way I needed it.
This query I'm using in a combo box, Table Name and RecCount.
Thanks,

My Query

SELECT MSysObjects.Name, DCount("*","[" & [Name] & "]") AS RecCountFROM MSysObjects
WHERE (((MSysObjects.Name) Not Like '~*' And (MSysObjects.Name) Not Like "TMPCLP" And (MSysObjects.Name) Like 'tblPartsB*') AND ((MSysObjects.type) In (1)))
ORDER BY MSysObjects.Name;

[orange]

tabledeff should be tabledef

[CJ_London]

You’ll need a function to return the recordcount From tabledefs. Perhaps something like

function rcount(tname as string) as long
rcount=tabledefs(tname).recordcount
end function

Free typed so may be errors

[isladogs]

I disagree and don't have any problems with querying MSysObjects.
It is read only and you cannot do any harm by querying it.
As far as I am concerned, querying MSysObjects is a very useful tool in any serious developer's toolkit

However I would modify the query to get all local non-system tables:

SELECT MSysObjects.Name, DCount("*","[" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

You can reinstate the filter for tables starting tblPartsB* if you wish.

SELECT MSysObjects.Name, DCount("*","[" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "tblPartsB*") AND ((MSysObjects.type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

[MadTom]

Thanks Collin, Had a bit of setup for my combo box, but that does the trick.
Mad-Tom

SELECT [QueryPartsTables].[name], [QueryPartsTables].[RecCount] FROM QueryPartsTables;