Finding the number of tables in a MS Access DB 2

[tztexas]

I can query a specific table, however I want to be able just to know how many tables are in a specific database.
Any ideas? thanks

 

[RoyVidar]

[tt]select count(*)
from msysobjects
where left(name,3)<>"msy")
and type =1;[/tt]

type = 1 is native tables. I think type = 4 represents tables linked to SQL Server/MSDE, and type = 6 represents linked Access tables.

Roy-Vidar

 

[FancyPrairie]

In addition to excluding system tables (Msys) you might want to include, in the SQL statement, a check to eliminate temporary data files. Temporary data files begin with "~".

 

[tztexas]

Thanks to both of you for your responses.

I tried yours Roy, but I must be doing something wrong.
"msysobjects" is new to me. I tried that verbatim, then I replaced it with my Database name...neither worked for me.

on the third line, "name" and "msy", I'm not sure about these either. Is name my DB? Since some of this language is new to me, I tried the entire example as is.

thanks

 

[PHV]

In the SQL pane of the Query window:
SELECT Count(*) AS CountOfTable
FROM msysobjects
WHERE Not ([name] Like 'msy*' Or [name] Like '~*') AND Type=1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

[tt]select count(*)
from msysobjects
where left(name,3)<>"msy"
and left(name,1)<>"~"
and type = 1;[/tt]

This you should be able to paste directly into the sql view of a query (last one had one closing parens to much, and missed temp tables, as mentioned by FancyPrairie). Access will probably add some [brackets]. Msysobjects is one of the system tables that is normally not visible in the database. In tools | options - view tab, check System Tables, to be able to see them. Msysobjects contains information on all the main objects of the database.

Roy-Vidar

 

[tztexas]

Thanks to all of you.

I tried Roy's and PHV's code, and I get the same error on both scripts.

msg 208 - Invalid object name 'msysobjects'

I'm running this from the SQL Query Analyzer

 

[PHV]

So don't ask in an Access forum but in a SQL Server one ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Erm - query analyser, thats one of the client tools of SQL server, if I'm not mistaken - are you sure you're working with Access? Both queries should work nicely within Access query builder.

For SQL server, I think the statement goes something like

[tt]select name from sysobjects where xtype = 'u'[/tt]

Roy-Vidar