How to resolve a Runtime Error '7874' Microsoft Access can't find the object 'tblBrEmailRout' and should I use a temporary table ?

I am using Access as the front end and SQL Server as the back end database for my application. I execute a stored procedure named
procBrEmailRecon which creates a table.

I then use  DoCmd.TransferSpreadsheet acExport, 8, "tblBrEmailROut", ExportedFile, True, ""
to send the table to Excel.
I run my application and the table tblBrEmailROut is out there. I see it in Enterpise Manager. However, I get a message:

Runtime Error '7874'
Microsoft Access can't find the object 'tblBrEmailRout'

Do you know why this error occurs despite the table being created and how I can resolve this error ?

DoCmd.TransferSpreadsheet acExport, 8, "tblBrEmailROut", ExportedFile, True, ""  <-------------highlighted line when error occurs

Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procBrEmailRecon"
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
    End With
   
    ExportedFile = "C:\UDL\DONTESCHEAT" & "_" & intYearSP & "_" & Format(Now, "mmddhhnnss") & ".XLS"
   
    DoCmd.TransferSpreadsheet acExport, 8, "tblBrEmailROut", ExportedFile, True, ""
    Beep
    MsgBox "Do Not Escheat reconciliation records have been exported to Excel", vbOKOnly, ""
    If isFileExist(ExportedFile) Then StartDocXLS ExportedFile

CREATE PROCEDURE dbo.procBrEmailRecon
AS
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblBrEmailROut' AND TYPE = 'U')
DROP TABLE tblBrEmailROut
SELECT O.[CPS Account Number], O.[Escheat Y/N], 'Y' As [Item On Download ?], 'Y' As [Error]
INTO tblBrEmailROut
FROM tblBRAttachOut As O, tblCustomers As C
WHERE Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber = O.[CPS Account Number] AND O.[Escheat Y/N] = '  '
Union ALL
SELECT O.[CPS Account Number], O.[Escheat Y/N], 'Y' As [Item On Download ?], 'N' As [Error]
FROM tblBRAttachOut As O, tblCustomers As C
WHERE Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber = O.[CPS Account Number] AND O.[Escheat Y/N] = 'Y'

Union ALL
SELECT O.[CPS Account Number], O.[Escheat Y/N], 'N' As [Item On Download ?], 'Y' As [Error]
FROM tblBRAttachOut As O
WHERE O.[Escheat Y/N] =  'Y'  AND  O.[CPS Account Number]  NOT IN (SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber from tblCustomers AS C)

Union ALL
SELECT O.[CPS Account Number], O.[Escheat Y/N], 'N' As [Item On Download ?], 'N' As [Error]
--INTO tblBrEmailROut
FROM tblBRAttachOut As O
WHERE O.[Escheat Y/N] =  'N'  AND O.[CPS Account Number]  NOT IN (SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber from tblCustomers AS C)  
ORDER BY O.[Escheat Y/N], O.[CPS Account Number]
GO
-------------------------------------------------------------

Let's say that I want to run a SELECT statement via a stored procedure and save the contents to a table from which I will transfer the
data to Excel, should I create a temporary table and if so, could you show me the code to do so ? You can reuse my stored procedure for illustrative purposes. I run the same stored procedure over and over again. First I delete the table if it exists and then SELECT INTO the table.

How would I create a table so that if multiple users run the same stored procedure that saves the results set to a table, that the users
won't be using the same table and clobbering each others data ?
 
I should mention that I am using an ADP file for my Access application.
 
 
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PSSUserCommented:
I believe the problem may be that the table has been created on the SQL Server back end but not linked to through the MS Access front end.

If this was a normaly MS Access mdb file with linked tables I would say this was definitely the problem, but I've never used an ADP file before so I'm not sure if the linked table will be created automatically when the SQL server table is.

You say you can see the table in Enterprise Manager but can you see it through the MS Access front end? If not you will need to create a linked table in the fornt end before you can use the transfer command. If you need to know the code to do this try looking at the code I posted here:http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21529210.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.