CODEWITHSUNDEEP
vbams-accesstimeoutms-access-2010
freesoft
freesoft

Reputation: 1144

Access VBA: How to change the query timeout when setting RecordSource

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:

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:

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.

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?

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:

CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120

that sets 2 minutes as timeout for the Access query object used by my SQL queries.

So now I set the timeout and then change the RecordSource property of the subform:

CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence

Upvotes: 4

Views: 11165

Answers (2)

Smandoli
Smandoli

Reputation: 7019

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:

CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120

that sets 2 minutes as timeout for the Access query object used by my SQL queries.

So now I set the timeout and then change the RecordSource property of the subform:

CurrentDb.QueryDefs("<my_access_query_object>").ODBCTimeout = 120
Forms("<main form>")("<subform>").Form.RecordSource = my_sql_sentence

Upvotes: 1

ingwy
ingwy

Reputation: 191

have a look at this, there might be something helpfull:

http://www.geeksengine.com/article/how-to-change-timeout-value-for-access-sql.html

Upvotes: 0

Related Questions