Help Please interfacing to SQL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I'm still a novice when it comes to getting MS Access talking to SQL

I have a pass through query in my access front end called

"sp_Subscriptions" which is currently set to NOT return records

Typically it will contain a little bit of T-SQL such as

exec QUEUE_INSERT 1000795, 76850452, 1, ''

At the moment it works well and will poke entries in to a processing queue
(that someone else takes care of)

Below is the stored procedure in the SQL database that it talks to(written
by someone else)

What I would like to do is get back in to Access the return value from this
stored procedure.

I know I need to change the query sp_Subscriptions to return records but
could anyone please tell how I need to change its syntax so that it returns
a single record with a single field that is the return value from the stored
procedure.

I know I am being a bit rude asking someone to just give me the answer,
instead of reading up on it myself, but I am really under tremendous
pressure from this client and I just don't have the time to spend a day
doing all the research to find an answer.

Many thanks in advance
Tony Epton


The stored procedure:=========


USE [Subscriptions]
GO
/****** Object: StoredProcedure [dbo].[Queue_Insert] Script Date:
10/06/2009 07:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Queue_Insert]
-- Add the parameters for the stored procedure here
@m_id int,
@GN int = NULL,
@QueueItemType tinyint,
@QueueItemDesc text = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RETVAL int

IF ISNULL(@GN, 0) = 0
BEGIN
DECLARE CUR_GN CURSOR FOR SELECT GN FROM tblMemberGNs WHERE MemberId =
@m_id
OPEN CUR_GN

FETCH NEXT FROM CUR_GN INTO @GN

WHILE @@FETCH_STATUS = 0
BEGIN

IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
END

FETCH NEXT FROM CUR_GN INTO @GN
END

CLOSE CUR_GN
DEALLOCATE CUR_GN
END
ELSE
BEGIN
IF NOT EXISTS(SELECT syncq_id FROM sync_queue WHERE syncq_type =
@QueueItemType AND syncq_m_growernum = @GN AND syncq_status = 1 AND
syncq_ts_started IS NULL)
BEGIN
INSERT INTO sync_queue (syncq_type, syncq_m_id, syncq_m_growernum,
syncq_status, syncq_ts_requested, syncq_ts_started, syncq_ts_completed,
syncq_short_desc) VALUES (@QueueItemType, @m_id, @GN, 1, GETDATE(), NULL,
NULL, @QueueItemDesc)
SET @RETVAL = ISNULL(SCOPE_IDENTITY(), 0)
END
END

RETURN @RETVAL
END




.

Relevant Pages

  • Re: Help Please interfacing to SQL
    ... set the pass-through query to return records and just run ... Below is the stored procedure in the SQL database that it talks to(written ... @GN int = NULL, ... NULL, @QueueItemDesc) ...
    (microsoft.public.access.queries)
  • Re: Stored Procedures v Views
    ... Build a stored procedure, ... The goal here will be to not use any dynamic sql like you used in Access. ... If the query is very complex, but frankly, if you have to do this, you are ... > they make are then used to create the SQL statement which is then used to ...
    (microsoft.public.sqlserver.programming)
  • RE: Class Module Choices
    ... SQL Server has Stored Procedures. ... Perhaps you meant Stored Query with is different. ... When I said stored procedure, ... I don't know how to assign a value to Param1 within the execute command. ...
    (microsoft.public.access.formscoding)
  • Re: Stored Procedure
    ... We cannot delare variables within the SQL statements we build in Access. ... If I want 14284 I should do a seperate query whereas in a stored procedure I ... This is preferable to having the client submitting all those queries ...
    (microsoft.public.access.modulesdaovba)
  • RE: How to simple retrieve autonumber valuees or identity from data so
    ... you can write the equivalent of a stored procedure in Access. ... Simply write your SQL in a query and save it. ... > I use the access database in my simple application. ...
    (microsoft.public.dotnet.framework.adonet)