无法读取记录；对“MSysObjects"没有读取权限 [英] Record cannot be read; no read permission on 'MSysObjects'

问题描述

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

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:

如何从 MS Access 数据库获取表名?

使用:

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

但我收到此错误.

无法读取记录；对MSysObjects"没有读取权限

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

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

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

我认为问题出在 Access 上.我不确定.

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

有人知道吗?

推荐答案

由于您的 db 是 ACCDB 格式，您将作为用户 Admin 工作.您可以在立即"窗口中确认该点.(使用 Ctrl+g 去那里)

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

由于 Admin 没有对 MSysObjects 的读取 (SELECT) 权限，执行 DDL 语句给 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

这篇关于无法读取记录；对“MSysObjects"没有读取权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！