Using temporary table in an ADO Recordset

Question

Please see the code below:

rsTitle.Open "CREATE TABLE #TestTable (testtitle varchar(10)) INSERT INTO #TestTable values ('TestTitle') select * from #testtable", objCon.ActiveCon, adOpenStatic, adLockReadOnly

rsTitle is always closed after the line above is executed. Why is this? It should return one row.

Answer 1

Try using SET NOCOUNT ON. Put SET NOCOUNT ON in the SQL statement used with ADO, as shown below:

Dim adoCn As adoDb.Connection
Dim adoCm As adoDb.Command
Dim rsTitle As adoDb.Recordset

Set adoCn = New adoDb.Connection
...    
Set adoCm = New adoDb.Command
With adoCm
    Set .ActiveConnection = adoCn
    .CommandType = adCmdText
    .CommandText = "CREATE TABLE #TestTable (testtitle varchar(10)) " & _
                   "INSERT INTO #TestTable(testtitle) values ('TestTitle') " & _
                   "SELECT * FROM #TestTable go"
    .Execute 
End With

Set rsTitle = New adoDb.Recordset
With rsTitle
    Set .ActiveConnection = adoCn
    .Open "SET NOCOUNT ON"
End With
rsTitle.Open adoCm, , , ,