system tables

[Guest]

How can I query Access sys tables? Need to query to get a list of all table
names for 5 separate databases. Where do I find the names of all tghe sys
tables?
Thanks!

 

[Jeff Conrad]

Make a query like so:

SELECT [Name]
FROM MsysObjects
WHERE (Left$([Name],1)<>"~")
AND (Left$([Name],4)<>"MSys")
AND (Type In (1.6))
ORDER By [Name]

See MVP Doug Steele's excellent article with download sample
on this subject here:

http://www.accessmvp.com/djsteele/SmartAccess.html

It is the May, 2004 article.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

in message:

 

[Douglas J. Steele]

SELECT MSysObjects.Name
FROM MSysObjects
IN 'D:\Data\External.mdb'
WHERE MSysObjects.Name Not Like "msys*"
AND MSysObjects.Type In (1,4,6)
ORDER BY MSysObjects.Name

 

[Guest]

Trying to query the MSysObjects object per your example below generates this
error for me:
Error: ACCESS:-1907[Microsoft][ODBC Microsoft Access Driver] Record(s)
cannot be read; no read permission on 'MSysObjects'.

Here's my connect string (using SQLTalk and an ODBC connection)
connect scheduler admin/; [in dev, no password yet]

I've looked through Access 2003 Tools, Database and Security menus but can't
see where I (as admin) can let myself get access to the system tables. Like
the previous writer, I'd like access to system tables for name, column, type,
length info. for my database. Thanks for any help!

Best Regards - Jeff

 

[Douglas J. Steele]

I'm afraid I have no knowledge of SQLTalk, so I can't suggest anything.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Trying to query the MSysObjects object per your example below generates
this
error for me:
Error: ACCESS:-1907[Microsoft][ODBC Microsoft Access Driver] Record(s)
cannot be read; no read permission on 'MSysObjects'.

Here's my connect string (using SQLTalk and an ODBC connection)
connect scheduler admin/; [in dev, no password yet]

I've looked through Access 2003 Tools, Database and Security menus but
can't
see where I (as admin) can let myself get access to the system tables.
Like
the previous writer, I'd like access to system tables for name, column,
type,
length info. for my database. Thanks for any help!

Best Regards - Jeff

SELECT MSysObjects.Name
FROM MSysObjects
IN 'D:\Data\External.mdb'
WHERE MSysObjects.Name Not Like "msys*"
AND MSysObjects.Type In (1,4,6)
ORDER BY MSysObjects.Name