excel - Record cannot be read; no read permission on 'MSysObjects'

Question

I'm trying to get a list of all tables from an Access 2007 ACCDB format database using Excel VBA.

I have followed this post:

How can I get table names from an MS Access Database?

Using:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE
        (((Left([Name],1))<>"~")
    AND ((Left([Name],4))<>"MSys")
    AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name

but I'm getting this error.

Record cannot be read; no read permission on 'MSysObjects'

I want to be able to get the table names only using a SQL statement and not the OpenSchema method.

I think the problem is with Access. I'm not sure.

Does anybody have any idea?

Answer 1

Since your db is ACCDB format, you will be working as user Admin. You can confirm that point in the Immediate window. (Go there with Ctrl+g)

? CurrentUser()
Admin

Since Admin doesn't have read (SELECT) permission on MSysObjects, execute a DDL statement to give Admin that permission.

strDdl = "GRANT SELECT ON MSysObjects TO Admin;"
CurrentProject.Connection.Execute strDdl