zimmer9
asked on
How to resolve the error Runtime Error '7874' Microsoft Access can't find the object 'tblBrEmailRout' ?
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
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
ASKER
OK then, 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 ?
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 ?
ASKER
I should mention that I am using an ADP file for my Access application.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just creating the table in SQL Server isn't enough. If you want to use TransferSpreadsheet, you need to have tblBrEmailROut
as an object in the Access db (as a linked table).
Regards,
Patrick