Is there SQL access to MS-Access MSysObjects?
 

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

Is there SQL access to MS-Access MSysObjects?
 

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

Nick

"quartz" wrote in message
...
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

Is there SQL access to MS-Access MSysObjects?
 

NickHK wrote:
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.

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

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;

Is ADOX the only other alternative?

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.

--

Is there SQL access to MS-Access MSysObjects?
 

Thanks to both of you...

"Jamie Collins" wrote:


NickHK wrote:
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.

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

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;

Is ADOX the only other alternative?

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.

--