troubleshooting Question

How to prevent Runtime error 7874: Microsoft Access can't find an object 'tblBrEmailROut' ?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution569 ViewsLast Modified:
In my Access application I use Access as the front end and SQL Server as the back end database.
I execute a stored procedure as follows which creates a table.
Then I execute a TransfeSpreadsheet command which transfers the table to an Excel file.
I ran the application and got the following message:

Runtime error 7874
Microsoft Access can't find an object 'tblBrEmailROut'

When I look at the tables via Enterprise Manager, initially I don't see this table tblBrEmailROut.
However, if I hit the Refresh icon, then the table appears in the tables section of Enterprise Manager.
Also, if I look at the sysobjects, I see the table was created.

Is there a refresh command that I could place between the Stored Procedure and the TransferSpreadsheet command ?
Why does this error occur and how can I prevent it ?

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, ""


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]
--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] = '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]
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros