在 VBA 中更改 tabledef .connect 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8391447/
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
Changing a tabledef .connect property in VBA
提问by user1082270
I am trying to do something that I have done a hundred times, but it suddenly won't work.
我正在尝试做一些我已经做过一百次的事情，但它突然不起作用。
acc.AutomationSecurity = msoAutomationSecurityLow 'Remove Security Prompt
acc.OpenCurrentDatabase path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " GENERIC DB NAME.mdb", True
'update link
acc.CurrentDb.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" & path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " OTHER DB.mdb"
acc.CurrentDb.TableDefs("TABLE NAME").RefreshLink
I don't know if this is related to the fact that we recently updated to Excel 2010 and the db is still ACCESS 2003 but this should be a simple bit of code to run. Nothing happens when I run the above code. The file opens the right way but the connection string never gets assigned to the table def object, ie. It runs without erroring, but the table is not linking to the new database. I have found the following code does work and have used it to get around the issue. I am still curious why I can't assign a value to the .connect property in the style 'application.currentdb.tabledef("TABLE").connect', but if I assign the currentdb to a new database object I can.
我不知道这是否与我们最近更新到 Excel 2010 并且数据库仍然是 ACCESS 2003 的事实有关，但这应该是一个简单的代码来运行。当我运行上面的代码时没有任何反应。该文件以正确的方式打开，但连接字符串永远不会分配给 table def 对象，即。它运行没有错误，但表没有链接到新数据库。我发现以下代码确实有效，并已使用它来解决该问题。我仍然很好奇为什么我不能在样式 'application.currentdb.tabledef("TABLE").connect' 中为 .connect 属性分配一个值，但是如果我将 currentdb 分配给一个新的数据库对象，我可以。
So I don't know why but if I use this it works
所以我不知道为什么，但如果我使用它，它会起作用
dim db as DAO.database
set db = acc.CurrentDb
db.TableDefs("TABLE NAME").Connect = "MS Access;DATABASE=" & path & "Year " & Range("yr") & "\" & Range("yr") & Range("mo") & "\Year " & Range("yr") & Range("mo") & " OTHER DB.mdb"
db.TableDefs("TABLE NAME").RefreshLink
Thanks for any help you can provide.
感谢您的任何帮助，您可以提供。
回答by Travis
I ran into a similar problem. When trying to do this directly through CurrentDb(i.e. CurrentDb.TableDefs("foo").Connect) didn't work and didn't throw an error.
我遇到了类似的问题。当尝试直接通过CurrentDb(ie CurrentDb.TableDefs("foo").Connect)执行此操作时，它不起作用并且没有抛出错误。
The reason is: Every time you refer to CurrentDB, it is a new instance.
原因是：每次引用CurrentDB，都是一个新的实例。
In your second method, you created a database object that you set to CurrentDb and that's your second method works.
在您的第二种方法中，您创建了一个设置为 CurrentDb 的数据库对象，这是您的第二种方法有效。
Long story short: Good:
长话短说：好：
Dim Db as Database
Set Db = CurrentDb
Db.TableDefs("foo").Connect = "New connection string"
Db.TableDefs("foo").RefreshLink
Bad:
坏的：
CurrentDb.TableDefs("foo").Connect = "New connection string"
CurrentDb.TableDefs("foo").RefreshLink
