VBA Runs Stored Proc Twice?

[Guest]

I have a SQL Server 2000 stored procedure which tests fine and works
perfectly when
you manually launch it as a pass-through query in Access. What this proc
does is
simply find a record, set a safety lock field to True, then pass back to the
client
index number of that record. That's all.

The bizarre problem I have is when you invoke the pass-though query via VBA
code, it
launches twice instead of just once!! Only one record ID is passed back
while two
records get locked?? How??


<<< PROCEDURE CODE >>>
CREATE PROCEDURE GetAndLockOneRec
AS
DECLARE @outputparam2 numeric
SET @outputparam2 = (SELECT TOP 1 ScriptLockKey FROM dbo.Table1 WHERE
(APPL_RESULT IS NULL) AND RECORD_LOCK=0)
UPDATE dbo.Table1 SET RECORD_LOCK=1 WHERE ScriptLockKey = @outputparam2
SELECT @outputparam2 'ScriptLockKey'
GO

<<< Access Code to call this pass-through query >>>
Dim VarX As Variant
VarX = DLookup("[ScriptLockKey]", "qry_GetAndLockOneRec")

 

[Steve Jorgensen]

The best way to call a stored procedure from VBA code is to use the Execute
method of a temporary Querydef object created in DAO or a Command object in
ADO. This means you don't ahve to save objects into the front-end at run-time
(a generally bad idea), and you don't have to rely on inefficient and
potentially unpredictable calls like DLookup.

 

[Vadim Rapp]

Hello TedGrier:
You wrote in conference microsoft.public.access.adp.sqlserver on Thu, 21
Oct 2004 15:25:07 -0700:

T> I have a SQL Server 2000 stored procedure which tests fine and works
T> perfectly when
T> you manually launch it as a pass-through query in Access.

Access MDB, I guess? (this newsgroup is about ADP)


T> The bizarre problem I have is when you invoke the pass-though query via
T> VBA code, it
T> launches twice instead of just once!! Only one record ID is passed
T> back while two
T> records get locked?? How??

I reproduced this, and, apparently, that's the way dlookup works for some
reason.

This does not happen with

Dim VarX As Variant, rs As Object
Set rs = CurrentDb.OpenRecordset("qry_GetAndLockOneRec")
VarX = rs(0)
rs.Close

or

varx = currentproject.connection.execute("qry_GetAndLockOneRec")(0)

Vadim

 

[Guest]

Excelent idea. It works great. Thank You!