SQL Timeing out in VBA

[CptCrusty1]

I have an SQL STatement, save in three strings (sqlSelect, sqlFrom, sqlWhere) which I concatenate in a DoCmd.RunSql statement. If I were using QBE, I would right-click and reset the time out to a higher number. I know my issue is sheer volumen of records, so how do I change the time out in code? Here's my docmd.....:

DoCmd.RunSQL sqlNatSelect & " " & sqlNatFrom & " " & sqlNatWhere

Thanks in Advance.
RK

 

[PaulBricker]

How many records are we talking about and how complex is your sql statement. Have you tried using Debug.Pring sqlNatSelect & " " & sqlNatFrom & " " & sqlNatWhere
to see if you are getting the string you intended. I've run queries that took 30 minutes plus to run without any timeout errors. And that was 4 million records.

Paul

 

[PaulBricker]

That should be
Debug.Prin[red]t[/red]


Paul

 

[RoyVidar]

You may find executing the query through either the DAO database object or ADO connection object to be a bit faster. I don't know much about DAO, but for ADO, you can set CommandTimeout. The default is 30 seconds. Setting to 0, will make it wait indefinately...

[tt]dim cn as adodb.connection
set cn=currentproject.connection
cn.commandtimeout=0
cn.execute sqlNatSelect & " " & sqlNatFrom & " " & sqlNatWhere,,adcmdtext[/tt]

Roy-Vidar

 

[CptCrusty1]

Actually, I said to hell with it and created it in QBE, then ran DoCmd.OpenQuery ("qryYaddaYadda")

It was several less lines of code and ran acceptably.

But thanks anyway guys, its appreciated, as always.....

RK