I have a SQL query which adds this month's data to a data history table. So, the history table has the monthly cut-off date as a key field. I call the stored procedure from Access and it adds the data. But, if I change this months data and try to add it, obviously, it will not work because the key field already has this month's date in it. I have an IF EXISTS statement that checks whether this month is already in the History Table and, if so, then it ends the procedure.

I want to add a variable that send a message box back to Access saying something like "this date already exists, do you want to replace it?". Then , if the user clicks YES it will delete the current add and add the new data. Searching on line it seems I need an OUTPUT variable but I am struggling to understand how to use it

The SQL query I have (currently hardcoded with a month end date but will become a stored procedure with date variable) is:

 USE [MyDatabase]
GO
IF EXISTS(select * from [dbo].[Tbl_30_LE_Archive] where cutoff = '2022-06' )
BEGIN
   PRINT 'Already data for Period 2022-06'
 END 
ELSE

BEGIN
INSERT INTO [dbo].[Tbl_30_LE_Archive]
       ([CutOff]
       ,[COMMIT_ID]
       ,[Period]
       ,[Monthly]
       ,[SAP_ToDate]
       ,[ToGo]
       ,[TotalFCST])


SELECT '2022-06' as Cutoff
, Commit_ID
, Period
, Amount
, SAP_ToDate
, ToGo
, TotalFCST

 From [dbo].[MT_LE_This_Mnth] 

END

GO

If I run this code in SSMS it shows 'Already data for Period 2022-06' but I cannot see how to get the same result within Access if I run it as a pass through query

CodePudding user response：

Ok, assuming you have that PT query setup in code (one that you use over and over in VBA? (one that eliminates the need for any connection string in code, right?).

Then use this format for the store procedure:

CREATE PROCEDURE dbo.MyCoolProc
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM tblHotelsA WHERE HotelName = 'My Cool' )
    BEGIN
       SELECT 'Hotel Already Exists' AS MyMessage
    END
    ELSE
    BEGIN
         -- DO whatever and then return message
         SELECT '' AS MyMessage
    END
END

And now our VBA code to call any stored procedure - including this one?

The code can look like this:

Sub TestFun55()

   Dim strResult       As String
   
   With CurrentDb.QueryDefs("qryPassR")
       .SQL = "EXEC dbo.MyCoolProc"
       strResult = .OpenRecordset()(0)           
  End With
        
  If strResult <> "" Then
       MsgBox "Message from SQL = " & strResult
  End If
        
End Sub

So, we thus assume that the store procedure will return a value (one row), and thus we just shove the results into the strResult, and you quite much free to then display this return value.

Edit: how to create a query in Access

so, poster is having difficulty creating a pass-though query in Access.

So, fire up the query builder in Access.

like this:

Then close the table prompt box, this one:

Now, go to sql view, like this:

then choose pass-though from ribbon

Now display property sheet (if not already)

Ok, now setup the connection for the pt query (I will often cut paste from a existing working query). but, you can use the connection builder (but, that's a bit of a hassle).

Now, type in a test query, a select, ANYTHING. But, for this test, lets type in our command to call/run/use the stored procedure we assume you written and setup on sql server.

this:

And now we can run that proc, and we get this:

Once the above is verified to work?

THEN and ONLY then do we go back to trying this with VBA code.

That code was/is this:

Sub TestFun55()

   Dim strResult       As String
   
   With CurrentDb.QueryDefs("qryPassR")
       .SQL = "EXEC dbo.MyCoolProc"
       strResult = .OpenRecordset()(0)
       
  End With
        
  If strResult <> "" Then
       MsgBox "Message from SQL = " & strResult
  End If
        
End Sub

I mean, in our VBA, we COULD just run/open the query, but as above VBA code shows, we are free to change or setup the exec command in VBA - thus we can use our one qryPassR to run any sql server command - select query, or even the exec "some store proc" command.

So, get the query working BEFORE you use or try to run some VBA code.

ONLY AFTER you verify the pass-though query works, THEN you can try the above VBA code.