vba 无法读取记录；对“MSysObjects”没有读取权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1937703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Record cannot be read; no read permission on 'MSysObjects'
提问by VBGKM
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?
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 OpenSchemamethod.
我希望能够仅使用 SQL 语句而不是OpenSchema方法来获取表名。
I think the problem is with Access. I'm not sure.
我认为问题出在 Access 上。我不知道。
Does anybody have any idea?
有人有任何想法吗？
回答by HansUp
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 格式，因此您将作为用户Admin工作。您可以在“立即”窗口中确认该点。（用Ctrl+去那里g）
? CurrentUser()
Admin
Since Admindoesn't have read (SELECT) permission on MSysObjects, execute a DDL statement to give Adminthat permission.
由于Admin没有读取 ( SELECT) 权限MSysObjects，请执行 DDL 语句以授予Admin该权限。
strDdl = "GRANT SELECT ON MSysObjects TO Admin;"
CurrentProject.Connection.Execute strDdl
回答by Tony Toews
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
回答by zzzeek
Here's what worked for me, since this is the first SO question that comes up for this:
这是对我有用的方法，因为这是为此提出的第一个 SO 问题：
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
这是一个 MDB 文件。不知道别人怎么样。 我明白这不是问题所要求的。但是，许多其他通过谷歌到达这里的人也使用 StackOverflow 问题/答案，就像我一样，我正在使用 MDB。我希望这个答案对其他人有用。
打开 MS Access GUI。不知道如何在没有它的情况下做到这一点，抱歉，尽管这可能是可能的。
转到工具...选项...
单击“查看”选项卡
选择“隐藏对象”、“系统对象”
关闭标签
转到工具...安全...用户和组权限
选择包括 MSysObjects 在内的所有表名
单击所有“权限”复选框，以便它们为所有条目设置为“已选中”
根据需要申请/确定
回答by Kevin Ross
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
如果不这样做，您可能必须以执行并授予自己权限的用户身份登录到数据库
回答by VBGKM
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 文件一起工作。
