Can't execute UPDATE SPROC from code - Error 3065

[teddysnips]

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]

Try this:

qdfPT.ReturnsRe cords = False

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

EXEC qryUpdateCostOf Booking

<te********@hot mail.comwrote in message
news:17******** *************** ***********@f36 g2000hsa.google groups.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]

On 24 Jul, 12:13, "bcap" <[e-mail] 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