Is there SQL access to MS-Access MSysObjects?

[quartz]

I am using Office 2003 on Windows XP with ADO 2.8.

When I run the following SQL against an Access DB from Excel:

sSQL = "SELECT Name FROM MSysObjects WHERE Type = -32768;"

I get an "insufficient permissions" error. The DB is simple (only one
table), was created by me, and has no security applied.

Is it not possible to query MSysObjects from Excel using ADO to get at the
object names and properties in a DB?

Is ADOX the only other alternative?

TIA

 

[NickHK]

quartz,
If you look at the permissions/ownership of the table in Access, you will
see that most are own by "Engine".

Nick

 

[quartz]

Thanks to both of you...

Therefore, the OP could create a VIEW e.g.

CREATE VIEW OddView AS SELECT Name FROM MSysObjects WITH OWNERACCESS
OPTION;

or maybe even change the permissions e.g.

GRANT SELECT ON TABLE MSysObjects TO Developers;


Take a look at the ADO Connection object's OpenSchema method.
OpenSchema is generally more feature rich than ADOX (which itself uses
OpenSchema under the hood) but ADOX is required for missing information
from or bugs in OpenSchema methods e.g. the Autoincrement property for
a column springs to mind.

Jamie.