December 22, 2003 at 1:02 pm
Here is what I have:
Access 2002 sp1
SQL Server 2000 sp3
MDAC 2.7
OS Win 2k pro and XP Pro
##########################################################
Access Code
##########################################################
CurrentDb.QueryDefs("sp_CREATE_POOL_PARTY").SQL = "EXECUTE " & _
"sp_CREATE_POOL_PARTY " & _
lngClientID & ", " & lngPoolID & ", " & glngLoginID & ", '" & datPartyDate & "', '" & _
datStartTime & "', '" & datEndTime & "', " & intNumGuests & ", " & intNumGuards & ", " & _
CCur(Me.txtRateGuards) & ", " & dblTotalHrs & ", " & curTotalCost & ", " & fCB_Notice & _
", " & fCB_Size & ", " & fCB_ShockDay & ",'" & Replace(strSpecialRequest, "'", "''") & _
"', " & lngAG_EmpID
lng_ppID = DLookup("[ppID]", "sp_CREATE_POOL_PARTY")
##########################################################
Stored Procedure
##########################################################
CREATE PROCEDURE dbo.sp_CREATE_POOL_PARTY
@lngClientID int,
@lngPoolID int,
@glngLoginID int,
@datPartyDate datetime,
@datStartTime datetime,
@datEndTime datetime,
@intNumGuests int,
@intNumGuards int,
@RateGuards money,
@dblTotalHrs float,
@curTotalCost money,
@fCB_Notice bit,
@fCB_Size bit,
@fCB_ShockDay bit,
@strSpecialRequest varchar(255),
@lngAG_EmpID int
AS
INSERT INTO
tblPP
(
ClientID,
PoolID,
SchedByUserID,
PartyDate,
StartTime,
EndTime,
NumPeople,
NumGuards,
RateQuote,
TotalHours,
Amount,
CB_Notice,
CB_Size,
CB_ShockDay,
SpecialRequest,
AG_EmpID
)
VALUES
(
@lngClientID,
@lngPoolID,
@glngLoginID,
@datPartyDate,
@datStartTime,
@datEndTime,
@intNumGuests,
@intNumGuards,
@RateGuards,
@dblTotalHrs,
@curTotalCost,
@fCB_Notice,
@fCB_Size,
@fCB_ShockDay,
@strSpecialRequest,
@lngAG_EmpID
)
SELECT @@IDENTITY AS PPID
GO
Here is what I think is happening.
When Access looks at the sp it executes it to obtain its metadata, once it has the metadata it then executes it for the real thing. Only this causes the sp to execute twice.
Is there any way to stop the second execution of the sp?
Thor
December 22, 2003 at 1:17 pm
I don't Use Access this Fashion. I prefer Adp Files but what I believe is Happening is that you have an EXECUTE Statement in the definition and you EXECUTE The Lookup when retrieving the data!!
HTH
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply