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.QueryDefs("qryUpdateCostOfBookingPT")
qdfPT.Execute <--- Error thrown here
qdfPT.Close
Set qdfPT = Nothing

The query qryUpdateCostOfBookingPT is:

EXEC "qryUpdateCostOfBooking"

The SPROC is:

CREATE PROCEDURE qryUpdateCostOfBooking AS

BEGIN

UPDATE
tblAccounts
SET
tblAccounts.[BOOKING TOTAL] = tblBookings.TOTAL
FROM
tblbookings LEFT JOIN
tblAccounts ON tblbookings.BookingID=tblAccounts.BookingID
END
GO

[bcap]

Try this:

qdfPT.ReturnsRecords = False

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

EXEC qryUpdateCostOfBooking

<te********@hotmail.comwrote in message
news:17**********************************@f36g2000 hsa.googlegroups.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.QueryDefs("qryUpdateCostOfBookingPT")
qdfPT.Execute <--- Error thrown here
qdfPT.Close
Set qdfPT = Nothing

The query qryUpdateCostOfBookingPT is:

EXEC "qryUpdateCostOfBooking"

The SPROC is:

CREATE PROCEDURE qryUpdateCostOfBooking AS

BEGIN

UPDATE
tblAccounts
SET
tblAccounts.[BOOKING TOTAL] = tblBookings.TOTAL
FROM
tblbookings LEFT JOIN
tblAccounts ON tblbookings.BookingID=tblAccounts.BookingID
END
GO

[teddysnips]

On 24 Jul, 12:13, "bcap" <[e-mail]:

Try this:

qdfPT.ReturnsRecords = False

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

EXEC qryUpdateCostOfBooking

Marvellous! Thanks for the help.

Edward