troubleshooting Question

How to resolve the error Runtime Error '7874' Microsoft Access can't find the object 'tblBrEmailRout' ?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
Microsoft Access
4 Comments1 Solution4487 ViewsLast Modified:
I am using Access as the front end and SQL Server as the back end database for my application. I execute a storoed 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


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