Adodb-passthrough Query Time Out

[satishchandrat]

Intermittently getting time out error.The function calls a pass through query, with the form field values converted to procedure parameters. This function is housed in a module, called on a button click action from access form.
When executed succefully, it returns back an integer value, to the calling access form.
The underlying store proc, imports data from text files to tables. The store procedure has no issues when executed from managment studio using same login, as specified on ODBC connection string.
SQL server version is SQL 2008 express and access is 2003.
When pass through query is executed individually from access, the procedure is executed with no issues. pass through query name is "qry_LoadImportFiles".
Intrestingly, this function executes very well through the form button click, but sometimes, seeing time out errors.


----=====
Public Function ImportFiles(ChangeDtParam As Date, StartDtParam As Date, EndDtParam As Date) As Integer

Dim rs As ADODB.Recordset, cn As ADODB.Connection
Dim strTSQL As String
Dim tdf As TableDef

strTSQL = "EXEC uspImportLoadFiles " & " '" & ChangeDtParam & "'"
strTSQL = strTSQL & "," & " '" & StartDtParam & "'"
strTSQL = strTSQL & "," & " '" & EndDtParam & "'"


Set cn = New ADODB.Connection
cn.ConnectionString = Replace(CurrentDb.QueryDefs("qry_LoadImportFiles").Connect, "ODBC;", vbNullString)
CurrentDb.QueryDefs("qry_LoadImportFiles").SQL = strTSQL
cn.Open

Set rs = New ADODB.Recordset
rs.Open CurrentDb.QueryDefs("qry_LoadImportFiles").SQL, cn

ImportFiles = rs.Fields(0).Value

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Function
---====


Tried to rewrite using ADOX, as below.

--===
Public Function ImportFiles_adox(ChangeDtParam As Date, StartDtParam As Date, EndDtParam As Date) As Integer


Dim rs1 As ADODB.Recordset, conn As ADODB.Connection
Dim strTSQL As String
Dim strQueryName As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command



strQueryName = "qry_LoadImportFiles"

strTSQL = "EXEC uspImportLoadFiles " & " '" & ChangeDtParam & "'"
strTSQL = strTSQL & "," & " '" & StartDtParam & "'"
strTSQL = strTSQL & "," & " '" & EndDtParam & "'"


'--------------
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

Set cmd = cat.Procedures(strQueryName).Command
cmd.CommandText = strTSQL
Set rs1 = cmd.Execute


ImportFiles_adox = rs1.Fields(0).Value


End Function
--====
Query time out issue not noticed but the ADOX based function is executing the same store procedure 5 times, though the cmd.execute is called once.

Also tried changing the ODBC time out values to 0 and 1000. Neither values made any difference.

I am novice on Access-ADODB, but more into sql server side.
Any help greatly appreciated.

 

[SQL_Hell]

Setting cn.timeout = 0 doesn't fix it?

Can you post any error messages?