通过DAO链接表 [英] Link Table via DAO

问题描述

因此，我实质上是试图通过DAO将ACCDB中的表链接到我正在使用的数据库中，该ACCDB被密码加密.我正在做的前提是数据有点用户敏感"，所以我不想让每个用户都可以访问我前端(具有前端/后端拆分)中的该表，而只能访问特定用户.我想做的是检查计算机的用户名，然后在用户名正确的情况下允许前端链接到数据:

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:

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

这将返回运行时错误"3367"无法附加.集合中已经存在具有该名称的对象.

This returns runtime error '3367' Cannot Append. An object with that name already exists in the collection.

所以我想这样做:

For Each tbl In CurrentDb.TableDefs
Msgbox tbl
Next tbl

但是该表在我的数据库中不存在，那该怎么办?

But the table doesnt exist in my database, so what should I do?

推荐答案

仔细研究一下如何在CurrentDb中检查表名.此行在我的系统上引发错误#13，类型不匹配" :

Take a closer look at how you're examining the table names in CurrentDb. This line throws error #13, "Type mismatch", on my system:

Msgbox tbl

我认为您应该改用TableDef.Name:

I think you should ask for the TableDef.Name instead:

Msgbox tbl.Name

但是，我不确定这是这里唯一的问题.您似乎通过复制TableDef并将其添加到CurrentDb.TableDefs来尝试链接到另一个db文件中的表. IF ，您可以做到这一点，它不会为您提供到源表的链接，它将创建一个新的副本在CurrentDb中.但是我怀疑它是否可以正常工作.

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 TableDef and adding it to CurrentDb.TableDefs. IF you can make that work, it won't give you a link to the source table, it would make a new copy in CurrentDb. But I'm skeptical whether it can work at all.

您可以创建一个新的TableDef对象，设置其Name，Connect和SourceTableName属性，然后将其附加到CurrentDb.TableDefs.在Connect属性中包含数据库密码.

You could create a new TableDef object, set its Name, Connect, and SourceTableName properties, then append it to CurrentDb.TableDefs. Include the database password in the Connect property.

此处已在Access 2007中经过代码测试.

Here is code tested in 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

这篇关于通过DAO链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！