Connecting with SQL through VBA

Hi. I am having two main issues. Any help with these would be greatly appreciated.

I have a stored SQL procedure saved in the variable SqlTextFile. The code below is meant to call the SQL server (defined in myConStr), run the stored query, and then copy the results into my excel worksheet.

This code works fine when I run a query that is quick and outputs only 1 table. I am having two issues.

1). When I run this on SQL code that takes perhaps 2 minutes to run, I get a timeout error after 30 seconds. I believe I am seeting the timeout to longer than this, but clearly I am not. Any ideas on why this is?

2). The copying from the recordset at the end will only copy the first table that is output by the SQL query. How do I copy more than 1 table?

objMyConn.ConnectionString = myConStr
objMyConn.CommandTimeout = 90000
objMyConn.ConnectionTimeout = 90000
objMyConn.Open
Dim hFile As Long
hFile = FreeFile
Open SqlTextFile For Input As #hFile
sqlstatement = Input$(LOF(hFile), hFile)
'Set and Excecute SQL Command'
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = sqlstatement
objMyCmd.CommandType = adCmdText
' 'Open Recordset'
Set objMyRecordSet = objMyCmd.Execute
ActiveSheet.Cells(3, 1).CopyFromRecordset (objMyRecordSet)
Close #hFile


Thank you very much for taking the time and helping.

Hi. Thank you for your response.

1). I agree that the code setting the timeout to 90000 seconds is not working. Any idea how to fix it?

2). I am not at all familiar with looping through recordsets. Would you be able to post some example code? What is the syntax with dealing with multiple recordsets?

I am sorry but I don't know what code tags are. I would be happy to add them if you could explain.

Thanks again.

I see your link about code tags. Not sure how they wouold work with vba.