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

Question

Hello,

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

I have followed this post:

http://stackoverflow.com/questions/201282/how-to-get-table-names-from-access

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?

Thanks

Answer 1

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 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

Hello,

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.