vba 通过 DAO 链接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17932353/
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
Link Table via DAO
提问by Jason Bayldon
So I am essentially trying to link a table via DAO from an ACCDB that is password-encrypted into the DB I am working in. The premise of what I am doing is that the data is sort of "user sensitive" so I do not want to let every user have access to this table in my front end (have the front-end/back-end split), only specific users. What I would like to do is to check the username of the computer, then allow the front-end to link to the data if the username is correct:
所以我本质上是试图通过 DAO 将一个表从一个 ACCDB 链接到我正在工作的数据库中。我正在做的前提是数据有点“用户敏感”，所以我不想要让每个用户都可以在我的前端访问这个表（有前端/后端拆分），只有特定用户。我想要做的是检查计算机的用户名，然后如果用户名正确则允许前端链接到数据：
Select Case Environ("username") 'select case user environment name
Case "jsmith" 'if username is jsmith then
Set db = DAO.OpenDatabase("Audit.accdb", False, False, _
";pwd=adaudit12") 'create connection to my other db
Set tbl = db.TableDefs(14) 'selects the table via index
CurrentDb.TableDefs.Append tbl 'create a link to my current DB with this table (throws ex here)
Case Else
End Select
This returns runtime error '3367' Cannot Append. An object with that name already exists in the collection.
这将返回运行时错误“3367”无法附加。集合中已存在具有该名称的对象。
So I thought to do this:
所以我想这样做：
For Each tbl In CurrentDb.TableDefs
Msgbox tbl
Next tbl
But the table doesnt exist in my database, so what should I do?
但是我的数据库中不存在该表，我该怎么办？
回答by HansUp
Take a closer look at how you're examining the table names in CurrentDb. This line throws error #13, "Type mismatch", on my system:
仔细看看您是如何检查CurrentDb. 这一行在我的系统上抛出错误 #13, "Type mismatch"：
Msgbox tbl
I think you should ask for the TableDef.Name instead:
我认为你应该要求 TableDef.Name 代替：
Msgbox tbl.Name
However, I'm not sure that's the only problem here. You seem to be trying to link to a table in another db file by copying that TableDefand adding it to CurrentDb.TableDefs. IFyou can make that work, it won't give you a linkto the source table, it would make a new copyin CurrentDb. But I'm skeptical whether it can work at all.
但是，我不确定这是唯一的问题。您似乎试图通过复制TableDef并将其添加到.db 文件来链接到另一个数据库文件中的表CurrentDb.TableDefs。 如果你可以做这项工作，它不会给你一个链接到源表，它将使一个新的副本在CurrentDb。但我怀疑它是否能奏效。
You could create a new TableDefobject, set its Name, Connect, and SourceTableNameproperties, then append it to CurrentDb.TableDefs. Include the database password in the Connectproperty.
您可以创建一个新的TableDef对象，设置它Name，Connect和SourceTableName它附加属性，然后到CurrentDb.TableDefs。在Connect属性中包含数据库密码。
Here is code tested in Access 2007.
这是在 Access 2007 中测试的代码。
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim strDbFile As String
Dim strLinkName As String
Dim strPassword As String
Dim strSourceTableName As String
strDbFile = "C:\share\Access\MyDb.accdb"
strPassword = "foo"
strSourceTableName = "Contacts"
strLinkName = "link_to_contacts"
strConnect = "MS Access;PWD=" & strPassword & _
";DATABASE=" & strDbFile
Debug.Print strConnect
Set db = CurrentDb
Set tdf = db.CreateTableDef
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTableName
tdf.Name = strLinkName
db.TableDefs.Append tdf
Set tdf = Nothing
Set db = Nothing
回答by mwolfe02
Tables and queries share the same name space in MS Access. Chances are you have a query with the same name as the table you are trying to link.
表和查询在 MS Access 中共享相同的名称空间。您可能有一个与您尝试链接的表同名的查询。
Also, Environ("username")is easily spoofed. Consider using the API function GetUserNameinstead. Of course, if you need real security you'll want to upgrade your back-end to SQL Server (Express) or some other RDBMS.
此外，Environ("username")很容易被欺骗。请考虑改用 API 函数GetUserName。当然，如果您需要真正的安全性，您需要将您的后端升级到 SQL Server (Express) 或其他一些 RDBMS。
