Recently we have a requirement which use macro to print some recordset value from a database table. Basically the VBA code which reference to DB part in Word Macro is as below
Dim strSQL As String Dim rs As ADODB.Recordset Dim totalcount As Integer strConn = "Provider=SQLOLEDB.1; Data Source= myserver\myinstance;Initial Catalog=mydb; Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConn Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandText = "SpGetSomethingFromSomeDbTable" cmd.CommandType = adCmdStoredProc cmd.Parameters.Refresh cmd.Parameters(1).Value = CInt(divisionid) cmd.Parameters(2).Value = 1 Set rs = cmd.Execute() totalcount = 0 Do While Not rs.EOF ReDim Preserve membersFor(totalcount) As memberinfo membersFor(totalcount).field1 = RemoveQCInLastName(rs("fieldname1")) membersFor(totalcount).field2 = RemoveQCInLastName(rs("fieldname2")) membersFor(totalcount).field3 = RemoveQCInLastName(rs("fieldname3")) rs.MoveNext totalcount = totalcount + 1 Loop
And part of the stored procedure is as below
if object_id('tempdb..#mytemptable') IS NOT NULL drop table #mytemptable ;with table1_CTE as(SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY field1, field2 ORDER BY FieldDateTime DESC) AS rn FROM AnotherDatabase.dbo.table1 WHERE fieldDateTime < @specificdate) as T WHERE rn = 1 AND IsFlag = 1 ) SELECT m.field3, a.field4, a.field5 into #mytemptable FROM table1_CTE m JOIN someothertable a ON m.samefield = a.samefield WHERE m.field1 = @currentvalue
When I run macro, the error “Operation is not allowed when the object is closed” popup and stop at line
Set rs = cmd.Execute()
Actually this issue is not due to temp table or CTE table but the insert/delete/update DDL operation in stored procedure. The root reason is the count of the number of rows affected by those DDL statements will return as part of the result set. So the solution is easy and simple just need to add the following behind “AS” before the “BEGIN” in the stored procedure.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
It took me 2 hours to figure out, hope it is helpful.