Finding a list of tables in Access from VBA in Excel

[ToDieFor]

I am trying to get a list of tables from Access in an Excel VBA program that
I'm writing. I have the query set up in Access that specifies this statement:
SELECT msysobjects.Name, msysobjects.Type
FROM msysobjects
WHERE (((msysobjects.Name) Not Like 'msys*') AND ((msysobjects.Type)=1 Or
(msysobjects.Type)=6));
It works from within Access, but when I ask Excel to use ADO and run "Select
* from [<Name of Query>]", I get an access denied error message. The
connection is using the "Admin" user information, and yet I get the access
denied message. I need to know if there is something within ADO that is
blocking the connection or is it a limited amount of access to the
MSysobjects outside of the Access program, and if possible a way to correct
the problem.

 

[NickHK]

I would expect some sort of access restriction on Access objects, but not
sure if they are actually blocked.
Are they visible in Access ? I would assume if they are hidden, they would
not available.

ADO has the OpenSchema method for this.

NickHK

 

[ToDieFor]

Where would I find the OpenSchema method? Is that in the .NET framework that
you are referring to? I am using Microsoft Excel to write the program / VBA
application. This uses the syntax from VB 6. When I play with the options
available, I find one that closes the recordset as soon as the command is
executed. Is there another way to get a list of tables and queries from
Access without using the sql statement that I came up with?

I would expect some sort of access restriction on Access objects, but not
sure if they are actually blocked.
Are they visible in Access ? I would assume if they are hidden, they would
not available.

ADO has the OpenSchema method for this.

NickHK

I am trying to get a list of tables from Access in an Excel VBA program that
I'm writing. I have the query set up in Access that specifies this statement:
SELECT msysobjects.Name, msysobjects.Type
FROM msysobjects
WHERE (((msysobjects.Name) Not Like 'msys*') AND ((msysobjects.Type)=1 Or
(msysobjects.Type)=6));
It works from within Access, but when I ask Excel to use ADO and run "Select
* from [<Name of Query>]", I get an access denied error message. The
connection is using the "Admin" user information, and yet I get the access
denied message. I need to know if there is something within ADO that is
blocking the connection or is it a limited amount of access to the
MSysobjects outside of the Access program, and if possible a way to correct
the problem.