记录不能被阅读;没有“MSysObjects”的阅读权限

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

我正在尝试使用Excel VBA从Access 2007 ACCDB格式数据库中获取所有表的列表。

I have followed this post:

我一直在关注这篇文章:

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

如何从MS 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'

记录不能被阅读;没有“MSysObjects”的阅读权限

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

我希望能够仅使用SQL语句而不是OpenSchema方法获取表名。

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

我认为问题在于访问。我不确定。

Does anybody have any idea?

有人知道吗?

5 个解决方案

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

由于您的db是ACCDB格式，您将作为用户管理员工作。您可以在直接窗口中确认这个点。(去那里Ctrl + g)

? CurrentUser()
Admin

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

因为Admin没有在MSysObjects上读取(SELECT)权限，所以执行一个DDL语句来授予Admin这个权限。

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

#2

Use the DAO tabledefs collection

使用DAO tabledefs集合。

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

#3

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, * 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.

这是一个MDB文件。不知道其他人。我明白这不是我要问的问题。但是，*问题/答案也被许多其他人通过谷歌到达这里，正如我所做的，我正在使用MDB。我希望这个答案对其他人有用。
Open MS Access GUI. Didn't figure out how to do this without it, sorry, though it's likely possible.

开放的女士访问GUI。如果没有它，我就不知道该怎么做，抱歉，这很有可能。
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

选择所有表名，包括MSysObjects。
click all the "permissions" checkboxes so they set up as "checked" for all entries

点击所有的“权限”复选框，以便他们为所有条目设置“检查”。
apply/OK as needed

应用/好的

#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

如果您不这样做，您可能必须作为一个用户登录到数据库并授予自己权限。

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

我能够使代码与MDB文件一起工作。我可以选择使用“数据库工具——用户和权限”来设置用户权限。此选项仅用于MDB文件。现在的问题是让它与ACCDB文件一起工作。

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.

问题是我不能让它与ACCDB文件一起工作。

#1