Use ADO recordset and connection to update same #TEMP table

Question

In VBA, I am trying to use an ADO recordset and a connection to perform separate operations on a SQL Server #TEMP table.

I would think that in the code below the recordset and the connection are using the same session/scope, and therefore the #TEMP table could be accessed using either object. This does not appear to be the case. The recordset appears to see the #TEMP table created by the connection, but the connection doesn't appear to see the records inserted by the recordset. And the recordset returns no records after the attempted deletion by the connection object.

I am wondering why this is, and how I can do it right.

EDIT: The code below fails whether I use a local #TEMP table or a global ##TEMP table.

Dim cn As New adodb.Connection
Dim rst As New adodb.Recordset

'Open Connection.
cn.Open cConnSeasFcst

'Create temp table.
cn.Execute "CREATE TABLE #TEMP (COL1 INT)"

'Open rst.
With rst
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockBatchOptimistic
End With

'Add records to temp table using rst.
rst.Open "#TEMP"
For i = 1 To 5
    rst.AddNew
    rst!COL1 = i
    rst.Update
Next

'Delete records using connection.
cn.Execute "DELETE #TEMP WHERE COL1 IN(2,4)", raff

'Display.
rst.Requery
While Not rst.EOF               ' ##ALWAYS AT EOF. RECORDS ARE NOT RETURNED.##
    Debug.Print rst!COL1
    rst.MoveNext
Wend

The final recordset requery does not return any records at all, and the raff variable is zero -- it appears to me that the connection object can't see any of the records inserted by the recordset.

Possible duplicate of [Scope of temporary tables in SQL Server](http://stackoverflow.com/questions/18614344/scope-of-temporary-tables-in-sql-server) — user692942, Feb 27 '17 at 21:36
Although it [could be this](http://stackoverflow.com/a/37073685/692942), try setting `SET NOCOUNT ON;`. — user692942, Feb 27 '17 at 21:40
@Lankymart The same code fails with a global ##TEMP table. I just tried `SET NOCOUNT ON;` as well and that fails too. — JBStovers, Feb 27 '17 at 21:48
Why would you be using `CursorLocation = adClient`? Have you tried just using an `ADODB.Recordset` with the defaults first instead of trying elaborate cursor and locking combinations? Breaking the code down to its simplest form would be the first thing you should be trying. — user692942, Feb 27 '17 at 22:07

score 2 · Accepted Answer · answered Feb 28 '17 at 02:50

I think the problem is that you are not closing the recordset before you issue the delete statement. i also changed the locktype, but that is probably a secondary issue. this works for me:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer

Const connString As String = "Provider=sqloledb;Data Source=yourserver;Initial Catalog=yourdb;Integrated Security=SSPI;"

'Open Connection.
cn.Open connString

'Create temp table.
cn.Execute "CREATE TABLE #TEMP (COL1 INT)"

'Open rst.
With rst
    .ActiveConnection = cn
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
End With

'Add records to temp table using rst.
rst.Open "#TEMP"
 For i = 1 To 5
    rst.AddNew
    rst!COL1 = i
    rst.Update
 Next
rst.Close

'Delete records using connection.
cn.Execute "DELETE FROM #TEMP WHERE COL1 IN (2,4)"

'Display
rst.Open "#TEMP"
While Not rst.EOF
    Debug.Print rst!COL1
    rst.MoveNext
Wend
rst.Close

Use ADO recordset and connection to update same #TEMP table

1 Answers1