I have a stored procedure that is loading data into a global temp table.
Here is all the code for that stored procedure.
CREATE PROCEDURE [BSO_ExportPMRecords]
AS
SET NOCOUNT ON
BEGIN
If EXISTS (Select * FROM Tempdb.dbo.sysobjects WHERE name =
"##TempPMValues")
Begin
Drop Table [##TempPMValues]
End
CREATE TABLE [##TempPMValues] (
[Monitor] [varchar] (20) NULL ,
[Coverage Area] [varchar] (20) NULL ,
[Service Co] [varchar] (20) NULL ,
[BranchSiteCode] [varchar] (20) NULL ,
[BankName] [varchar] (50) NULL ,
[BankAddress] [varchar] (50) NULL ,
[BankCity] [varchar] (30) NULL ,
[BankState] [char] (2) NULL ,
[BankZip] [varchar] (15) NULL ,
[DateInstalled] [datetime] NULL ,
[PM Type] [varchar] (15) NULL ,
[Last PM Date] [datetime] NULL ,
[Next PM Scheduled] [datetime] NULL
)
INSERT INTO ##TempPMValues SELECT * FROM PMLog ORDER BY [Coverage Area],
[Next PM Scheduled]
END
I am using DoCmd.TransferSpreadsheet to put the temp table data into a
spreadsheet. Here is that code.
DoCmd.TransferSpreadsheet acExport, 8, "##TempPMValues", sFileName
When this line executes, I see "Error 7874. Microsoft Access can't find the
object '##TempPMValues.'
I have tried both "dbo.##TempPMValues" and "tempdb.##TempPMValues" in the
TransferSpreadsheet action. I can use "Select * from ##TempPMValues" in
query analyzer and the temp table does exist.
How can I use a global temp table in TransferSpreadsheet?
Thanks,
George