Running SQL Query From Excel Macro

[pmurch]

I have a stored procedure that runs on an SQL server (running SQL Server 2008 R2 SP2), updating an SQL table.

I also have an Excel 2007 spreadsheet with a macro that opens a recordset to the SQL stored proc, initiating the update of the SQL table.

Through initial development, there were no problems using the macro to kick off the stored proc. However, the next step of the development process requires taking a data snapshot before and after the update. When we add the creation of a log table for this purpose, the Excel macro stops working, issuing an 8004E021 error. We've tried both in-memory and disc-based tables, both returning the same error. Remove the logging code (particularly the creation of the table), and the script runs successfully. We've also tried separate scripts that only create a table -- no other processing -- and they fail as well.

Is there some setting in SQL (or Excel, perhaps, or VBA) that will allow a VBA macro to execute a SQL stored proc that creates a table during execution?

Thanks in advance for any advice!

 

[PHV]

a VBA macro to execute a SQL stored proc
What is your actual code doing that ?

a macro that opens a recordset to the SQL stored proc
I'd execute a command instead of open a recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pmurch]

Thanks for replying, PHV. Below is the calling section of the VBA code from the macro. I probably worded my post unclearly -- I do use a command to kick off the stored proc (see "adCmdText" in the Options parameter of the Open). Again, the calling seems to work fine -- without making any changes to the macro, simply adding or removing the CREATE TABLE code on the stored proc makes or breaks the macro.

cnnConnect.Open "Provider=SQLOLEDB.1;Initial Catalog=" & dbnShort _
& ";Data Source=mysqlserver;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" _
& "Use Encryption for Data=False;Tag with column collation when possible=False;" _
& "User ID=thisuser;Password=mypw;"
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:="exec DB_GLOBAL.dbo.[USR_Cred2_DisplayUncompletedCreds-Excel] '" _
& dbnShort & "', '" & DBList(idxDB, 2) & "'", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

'Add the new QueryTable to the new worksheet and populate it from rstRecordset
With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
Destination:=Range(wsName & "!A6"))
.Name = "AMC_BLSQL1_" & wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh (False)
End With