How to resolve the Run time error '-2147217900(80040e14)' ? There is already an object named 'tblDtlRange' in the database
Do you know how I can resolve the following error message:
Run time error '-2147217900(80040e14)'
There is already an object named 'tblDtlRange' in the database
--------------------------
the highlighted line is:
Set rstQueryFS = .Execute
--------------------------
Set com = New ADODB.Command
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procDetailRangeRpt"
'.Parameters.Append .CreateParameter("RptYear"
.ActiveConnection = cn
Set rstQueryFS = .Execute <--------- highlighted line when runtime error occurs
End With
intReport = intYearSP + 1
ExportedFile = "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"
DoCmd.TransferSpreadsheet acExport, 8, "tblDtlRange", ExportedFile, True, ""
Beep
MsgBox "Detail Range Report has been exported to Excel", vbOKOnly, ""
If isFileExist(ExportedFile) Then StartDocDetail ExportedFile
--------------------------
CREATE PROCEDURE procDetailRangeRpt
AS
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblDtlRange ' AND TYPE = 'U')
DROP TABLE tblDtlRange
SELECT C.OfficeNumber, C.CustomerNumber, C.ResStateCode, C.ResCountryCode, C.CitizenCode, C.DateLost,
C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName, C.StreetAddr1,
C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag,
tblMthRanges.MthFrom, tblMthRanges.MthTitle,
--CASE WHEN P.MarketValue IS NULL THEN P.CashBalance ELSE P.MarketValue END AS [Acct Value],
Sum(isnull(MarketValue,0)+
CONVERT(char(10), GETDATE() - Day(GETDATE()) + 1, 121) AS DFrom,
CONVERT(char(10), DATEADD(Month, -[MthTo] + 1, GETDATE() - Day(GETDATE()) + 1), 121) AS DateFromC,
tblMthRanges.MthTo,
CONVERT(char(10),DATEADD(M
INTO tblDtlRange
FROM tblMthRanges, tblCustomers As C INNER JOIN tblProducts As P ON C.CustomerNumber=P.Custome
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DA
AND [MthFrom] = 0 AND [MthTo] = 6
GROUP BY
tblMthRanges.MthFrom, tblMthRanges.MthTitle, C.OfficeNumber, C.CustomerNumber,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DAT
C.CitizenCode, C.DateLost, C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName,
C.StreetAddr1, C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag
GO
Run time error '-2147217900(80040e14)'
There is already an object named 'tblDtlRange' in the database
--------------------------
the highlighted line is:
Set rstQueryFS = .Execute
--------------------------
Set com = New ADODB.Command
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procDetailRangeRpt"
'.Parameters.Append .CreateParameter("RptYear"
.ActiveConnection = cn
Set rstQueryFS = .Execute <--------- highlighted line when runtime error occurs
End With
intReport = intYearSP + 1
ExportedFile = "C:\UDL\DTLRANGE" & "_" & intReport & "_" & Format(Now, "mmddhhnnss") & ".XLS"
DoCmd.TransferSpreadsheet acExport, 8, "tblDtlRange", ExportedFile, True, ""
Beep
MsgBox "Detail Range Report has been exported to Excel", vbOKOnly, ""
If isFileExist(ExportedFile) Then StartDocDetail ExportedFile
--------------------------
CREATE PROCEDURE procDetailRangeRpt
AS
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblDtlRange ' AND TYPE = 'U')
DROP TABLE tblDtlRange
SELECT C.OfficeNumber, C.CustomerNumber, C.ResStateCode, C.ResCountryCode, C.CitizenCode, C.DateLost,
C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName, C.StreetAddr1,
C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag,
tblMthRanges.MthFrom, tblMthRanges.MthTitle,
--CASE WHEN P.MarketValue IS NULL THEN P.CashBalance ELSE P.MarketValue END AS [Acct Value],
Sum(isnull(MarketValue,0)+
CONVERT(char(10), GETDATE() - Day(GETDATE()) + 1, 121) AS DFrom,
CONVERT(char(10), DATEADD(Month, -[MthTo] + 1, GETDATE() - Day(GETDATE()) + 1), 121) AS DateFromC,
tblMthRanges.MthTo,
CONVERT(char(10),DATEADD(M
INTO tblDtlRange
FROM tblMthRanges, tblCustomers As C INNER JOIN tblProducts As P ON C.CustomerNumber=P.Custome
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DA
AND [MthFrom] = 0 AND [MthTo] = 6
GROUP BY
tblMthRanges.MthFrom, tblMthRanges.MthTitle, C.OfficeNumber, C.CustomerNumber,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DAT
C.CitizenCode, C.DateLost, C.DateOfBirth, C.SSN, C.TaxId, C.FixedFieldInd, C.FirstName, C.MiddleInitial, C.LastName,
C.StreetAddr1, C.StreetAddr2, C.City, C.State, C.Zip, C.Country, C.RedFlag
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went with option 5, set up another stored procedure to execute the solely the following statement since it
doesn't seem to work when I call it before the SELECT INTO statement in my original stored procedure.
The strange thing is that this If statement test works fine in all my other stored procedures when I execute it.
I performed a DoCmd.TransferSpreadsheet on the SELECT INTO table.
then I call a stored procedure to delete the table as follows:
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblDtlRange ' AND TYPE = 'U')
DROP TABLE tblDtlRange
doesn't seem to work when I call it before the SELECT INTO statement in my original stored procedure.
The strange thing is that this If statement test works fine in all my other stored procedures when I execute it.
I performed a DoCmd.TransferSpreadsheet on the SELECT INTO table.
then I call a stored procedure to delete the table as follows:
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = ' tblDtlRange ' AND TYPE = 'U')
DROP TABLE tblDtlRange
It looks like I missed the DROP TABLE in your original question and rafrancisco accurately diagnosed the problem.
Feel free to re-assign points. Here's how:
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
Feel free to re-assign points. Here's how:
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblDtlRange' AND TYPE = 'U')