CommandTimeOut - lesson learned

 Keywords:  

---

Lesson Learned:

I have wrestled with a sticky issue of performing an Inner Join between a sizeable Access Table ( say 322,000 rows ) and a table on an AS400 ( say half a billion rows ). First NOTE: I don't have rights to put the Access table on the 400 and perform the entire query there, heck, why make it easy.

Working to avoid the dreaded "ODBC Call - Failed" error. I would set up my ADODB.Connection Object, set both the ConnectionTimeOut and the CommandTimeOut to 0 ( which means they should not time out ), then issue a

DB.Execute( MySQLStatement ).

which I have always assumed acts as a Command. However, and the ADO docs say this, but it is easy to gloss over, the Command Object does not inherit from the Connection Object. Add to that there is a bug in the CommandTimeOut property of the Connection Object which Microsoft swears they fixed, but others swear is still a bug, which will fail if set to 0.

So the following,( assuming cConn is your Connection Statement ) is a workaround.

cmd = ObjectOpen("ADODB.Command")
cmd.ActiveConnection = cConn
cmd.CommandText = MySQLStatement
cmd.CommandTimeOut =0
cmd.Execute()

---

Article ID:   W15222