如何更改 VBA 中现有 MS-ACCESS 直通查询的 ODBC 连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7332703/
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
How do you alter the ODBC connection string for existing MS-ACCESS pass-through queries in VBA
提问by Acoats
I have a number of pass-through queries setup in a MS-Access database with pre-defined ODBC connection strings. The problem is the database can link to one of two MySQL databases. The user selects the db on starting the database and the system dynamically links the appropriate tables via VBA (this works fine) However I then need to alter the ODBC Connection strings in the existing queries to match the selected db. I therefore needa VBA function to loop through all the existing pass-through queries setting the ODBC connection string propertiy to the new connection string. Any ideas on how to do this?
我在带有预定义 ODBC 连接字符串的 MS-Access 数据库中设置了许多传递查询。问题是数据库可以链接到两个 MySQL 数据库之一。用户在启动数据库时选择数据库，系统通过 VBA 动态链接适当的表（这很好用）但是我然后需要更改现有查询中的 ODBC 连接字符串以匹配所选数据库。因此，我需要一个 VBA 函数来遍历所有现有的传递查询，将 ODBC 连接字符串属性设置为新的连接字符串。关于如何做到这一点的任何想法？
回答by Fionnuala
You can loop through the queries collection:
您可以遍历查询集合：
Dim qdf As QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Type = dbQSQLPassThrough Then
qdf.Connect = NewConnect
End If
Next
You could also examine the Connect string with InStr(qdf.Connect)to test for the existence of the required database name.
您还可以检查连接字符串InStr(qdf.Connect)以测试所需的数据库名称是否存在。
