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

Asked 21/12, 2009 at 1:6 Answered 3/2, 2013 at 4:59

excel ms-access ms-access-2007 vba

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?

Chitchat answered 21/12, 2009 at 1:6 Comment(9)

What is your programming environment? What are you going to use the results for? – Pasteurization 22/12, 2009 at 0:27

Also, what's your file format -- ACCDB or MDB? – Pasteurization 22/12, 2009 at 0:43

Thanks for your question. I'm using ACCDB. – Chitchat 22/12, 2009 at 12:48

Also, I'm using VBA to transfer information from Access to Excel. – Chitchat 22/12, 2009 at 22:48

Is your VBA in Excel or in Access? – Pasteurization 23/12, 2009 at 22:18

How are you attempting to open a on your SQL statement? ADO or DAO? Post code. There may be something wrong there, e.g., with a connection string. – Pasteurization 24/12, 2009 at 20:37

I'm using ADO. My code is one of the answers on this page that starts with "I was able to make the code ..." – Chitchat 25/12, 2009 at 13:29

Sorry -- I would have expected you to edit your original question rather than posting additional info as an answer. Anyway, have you tried changing your connect string to match the first one listed at connectionstrings.com/access-2007 ? – Pasteurization 26/12, 2009 at 21:5

I got my string connection from the same website. I included the part Persist Security Info=False; but I'm still getting the same error. – Chitchat 27/12, 2009 at 3:11

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

Fleck answered 3/2, 2013 at 4:59 Comment(2)

+1 Excellent! This also allows ACE/OLEDB to access MSysObjects in an .accdb file from outside of Access. – Archespore 25/2, 2013 at 20:45

Trying this with both an ODBC connection and with an access UI query window on an accdb file - and it's invalid SQL. – Urban 1/9, 2021 at 16:22

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

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.
Open MS Access GUI. Didn't figure out how to do this without it, sorry, though it's likely possible.
Go to Tools...Options...
Click "View" tab
select "Hidden objects", "System objects"
close tab
Go to Tools...Security.. User and Group permissions
Select all the table names including MSysObjects
click all the "permissions" checkboxes so they set up as "checked" for all entries
apply/OK as needed

Cupp answered 30/9, 2012 at 23:39 Comment(2)

I addressed that in point #1. The question here is of use to people using both formats. If you google for this error message, this is the SO question that comes up. So having a working answer for some is better than no answer at all. – Cupp 1/10, 2012 at 5:11

Upvoted because #1 is exactly what I was looking for a decade later when I stumbled on this. Thanks @Cupp – Thymol 1/3, 2022 at 16:0

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

Crisis answered 21/12, 2009 at 23:11 Comment(4)

That seems sensible, but the OP says "I want to be able to get the table names only using a SQL statement" – Decalcify 21/12, 2009 at 23:39

There's nothing in the original question suggesting that this is being done from outside Access, so it seems to me that if you're going to get a list of tables, you're going to do something with them. Short of using the SQL string as the rowsource of a combo/listbox, you're going to be using code to use the resulting list, in which case, it hardly makes any difference whether you use the TableDefs collection walk through the Recordset based on the SQL statement. – Pasteurization 22/12, 2009 at 0:27

Remou, sure, he said only using a SQL statement. I figure if he hasn't got an answer by now then he should try alternatives. – Crisis 22/12, 2009 at 1:5

To explain why I am here: I'm trying to list the tables in an *.mdb file using Perl on Cygwin; trying to do it with DBD::ODBC fails on this error, and the Win32::OLE module that can be used to do DAO won't install. – Doralia 25/2, 2011 at 13:18

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

Jackqueline answered 21/12, 2009 at 8:37 Comment(3)

-1 Tools->Security->User and Group Permissions does not exist in Access 2007 – Complect 8/3, 2010 at 4:38

@jaywon: that menu choice DOES exist if you're viewing an MDB file. It will not show up if you're viewing an ACCDB file, for which there is no support for Jet user-level security. So, your comment is WRONG. – Pasteurization 30/8, 2011 at 22:3

Question involves ACCDB, not MDB. This response does not answer the question. – Gilmore 17/1, 2023 at 5:21

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.

Chitchat answered 22/12, 2009 at 14:20 Comment(0)

Recommended topics

Hot tags