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

Here's what worked for me, since this is the first SO question that comes up for this:

1. this is an MDB file. Don't know about others. I understand this is not what the question asked for. However, StackOverflow questions/answers are also used by many other people arriving here via google, as I did, and I'm using MDB. I hope this answer is of use to someone else.

2. Open MS Access GUI. Didn't figure out how to do this without it, sorry, though it's likely possible.

3. Go to Tools...Options...

4. Click "View" tab

5. select "Hidden objects", "System objects"

6. close tab

7. Go to Tools...Security.. User and Group permissions

8. Select all the table names including MSysObjects

9. click all the "permissions" checkboxes so they set up as "checked" for all entries

10. apply/OK as needed

Answer 2

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