Can't execute UPDATE SPROC from code - Error 3065

[teddysnips@hotmail.com]

My client has recently upsized to a SQL Server back end. All is fine
except there is an UPDATE query that is very slow. I've tested it in
SQL Server and it runs fine, so I'd like to execute a SQL Server
Stored Procedure from the Access application in code. The way I've
done it is to "EXEC" the SPROC from within a pass-through query.
However, when I run it I get an Error 3065 "Cannot execute a select
query".

The code is as follows:

Dim qdfPT As DAO.QueryDef

Set qdfPT = CurrentDb.Query Defs("qryUpdate CostOfBookingPT ")
qdfPT.Execute <--- Error thrown here
qdfPT.Close
Set qdfPT = Nothing

The query qryUpdateCostOf BookingPT is:

EXEC "qryUpdateCostO fBooking"

The SPROC is:

CREATE PROCEDURE qryUpdateCostOf Booking AS

BEGIN

UPDATE
tblAccounts
SET
tblAccounts.[BOOKING TOTAL] = tblBookings.TOT AL
FROM
tblbookings LEFT JOIN
tblAccounts ON tblbookings.Boo kingID=tblAccou nts.BookingID
END
GO

[bcap]

Re: Can't execute UPDATE SPROC from code - Error 3065

Try this:

qdfPT.ReturnsRe cords = False

Also, lose the inverted commas around the SP name i.e.

EXEC qryUpdateCostOf Booking

<teddysnips@hot mail.comwrote in message
news:174543f0-a940-4d72-890b-f5de16e0f8e5@f3 6g2000hsa.googl egroups.com...

My client has recently upsized to a SQL Server back end. All is fine
except there is an UPDATE query that is very slow. I've tested it in
SQL Server and it runs fine, so I'd like to execute a SQL Server
Stored Procedure from the Access application in code. The way I've
done it is to "EXEC" the SPROC from within a pass-through query.
However, when I run it I get an Error 3065 "Cannot execute a select
query".
>
The code is as follows:
>
Dim qdfPT As DAO.QueryDef
>
Set qdfPT = CurrentDb.Query Defs("qryUpdate CostOfBookingPT ")
qdfPT.Execute <--- Error thrown here
qdfPT.Close
Set qdfPT = Nothing
>
The query qryUpdateCostOf BookingPT is:
>
EXEC "qryUpdateCostO fBooking"
>
The SPROC is:
>
CREATE PROCEDURE qryUpdateCostOf Booking AS
>
BEGIN
>
UPDATE
tblAccounts
SET
tblAccounts.[BOOKING TOTAL] = tblBookings.TOT AL
FROM
tblbookings LEFT JOIN
tblAccounts ON tblbookings.Boo kingID=tblAccou nts.BookingID
END
GO

[teddysnips@hotmail.com]

Re: Can't execute UPDATE SPROC from code - Error 3065

On 24 Jul, 12:13, "bcap" <b...@nospam.no wherewrote:

Try this:
>
qdfPT.ReturnsRe cords = False
>
Also, lose the inverted commas around the SP name i.e.
>
EXEC qryUpdateCostOf Booking

Marvellous! Thanks for the help.

Edward