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

Answer 2

Use the DAO tabledefs collection

Sub TableDefs()

    Dim db As dao.Database
    Dim tdfLoop As dao.TableDef

    Set db = CurrentDb
    With db
        Debug.Print .TableDefs.Count & " TableDefs in " & .name
        For Each tdfLoop In .TableDefs
            Debug.Print "    " & tdfLoop.name
        Next tdfLoop
    End With

End Sub

Answer 3

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 4

It looks like a permissions problem. Try opening the database and going to the security permissions (under Tools-> security -> User and group permissions) Make sure you have admin access to the database.

If you don’t you might have to logon to the database as a user that does and grant yourself permissions

Answer 5

I was able to make the code work with a MDB file. I had the option to set the user permissions using "Database Tools - Users and Permissions" on the ribbon. This option is only available for MDB files. Now the problem is to make it work with a ACCDB file.

Here is my code:

Dim DBFile As String  
Dim Connection As ADODB.Connection 
Dim Recordset As New ADODB.Recordset

DBFile = "C:\Documents and Settings\User\Desktop\Son.mdb"

Set Connection = New ADODB.Connection  <br/>
Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DBFile & ";"  

SQLString = "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" 

Set Recordset = New ADODB.Recordset 
Recordset.Open SQLString, Connection

The problem is that I can't make it work with ACCDB files.