访问 VBA：如何在设置 RecordSource 时更改查询超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22763018/
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
Access VBA: How to change the query timeout when setting RecordSource
提问by freesoft
In Access 2010 I have a form with a subform. Via VBA I create a SQL query and set the RecordSource of the subform to run the query and show the results:
在 Access 2010 中，我有一个带有子表单的表单。通过 VBA，我创建了一个 SQL 查询并设置子表单的 RecordSource 以运行查询并显示结果：
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence
Sometimes, the query produces a timeout error, so I detect it:
有时，查询会产生超时错误，因此我检测到它：
On Error Resume Next
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence
If Err <> 0 Then
MsgBox "Error! (probably timeout): " & Err.Description
End If
On Error GoTo 0
The timeout seems to be ~ 1 minute. I have tried to change the time for timeout by setting the QueryTimeout property of the CurrentDB object before changing the RecordSource property:
超时似乎是 ~ 1 分钟。我试图通过在更改 RecordSource 属性之前设置 CurrentDB 对象的 QueryTimeout 属性来更改超时时间：
CurrentDb.QueryTimeout = 2
On Error Resume Next
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence
If Err <> 0 Then
...
but it doesn't work, timeout is still 60.
但它不起作用，超时仍然是60。
If I try to change the timeout in the Inmediate panel, it has no effect:
如果我尝试在“中间”面板中更改超时，则无效：
? CurrentDB.QueryTimeout
60
CurrentDB.QueryTimeout = 2
? CurrentDB.QueryTimeout
60
How can I change the timeout for the SQL query that is run when I set the RecordSource property of a form?
如何更改在设置表单的 RecordSource 属性时运行的 SQL 查询的超时时间？
Update: Solved: There is a way to set the timeout for queries on ODBC connections (I use ODBC connection to Oracle). The SQL sencentes that I'm assigning to the RecordSource don't use a linked table, they use an Access query object. I mean that the SQL sentences are not like "select * from my_linked_oracle_table ...", they are more like "select * from my_access_query_object ...". So I can set the timeout for the QueryDef that corresponds to the Access query object, in this way:
更新：已解决：有一种方法可以为 ODBC 连接的查询设置超时（我使用 ODBC 连接到 Oracle）。我分配给 RecordSource 的 SQL 语句不使用链接表，它们使用 Access 查询对象。我的意思是 SQL 语句不像“select * from my_linked_oracle_table ...”，它们更像是“select * from my_access_query_object ...”。所以我可以通过这种方式为对应于 Access 查询对象的 QueryDef 设置超时：
CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
that sets 2 minutes as timeout for the Access query object used by my SQL queries.
将 2 分钟设置为我的 SQL 查询使用的 Access 查询对象的超时时间。
So now I set the timeout and then change the RecordSource property of the subform:
所以现在我设置超时，然后更改子窗体的 RecordSource 属性：
CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence
回答by Smandoli
There is a way to set the timeout for queries on ODBC connections (I use ODBC connection to Oracle). The SQL sencentes that I'm assigning to the RecordSource don't use a linked table, they use an Access query object. I mean that the SQL sentences are not like "select * from my_linked_oracle_table ...", they are more like "select * from my_access_query_object ...". So I can set the timeout for the QueryDef that corresponds to the Access query object, in this way:
有一种方法可以设置 ODBC 连接查询的超时时间（我使用 ODBC 连接到 Oracle）。我分配给 RecordSource 的 SQL 语句不使用链接表，它们使用 Access 查询对象。我的意思是 SQL 语句不像“select * from my_linked_oracle_table ...”，它们更像是“select * from my_access_query_object ...”。所以我可以通过这种方式为对应于 Access 查询对象的 QueryDef 设置超时：
CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
that sets 2 minutes as timeout for the Access query object used by my SQL queries.
将 2 分钟设置为我的 SQL 查询使用的 Access 查询对象的超时时间。
So now I set the timeout and then change the RecordSource property of the subform:
所以现在我设置超时，然后更改子窗体的 RecordSource 属性：
CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence
回答by ingwy
have a look at this, there might be something helpfull:
看看这个，可能有帮助：
http://www.geeksengine.com/article/how-to-change-timeout-value-for-access-sql.html
http://www.geeksengine.com/article/how-to-change-timeout-value-for-access-sql.html
