As promised here is the more detailed test code.  This behaves
similarly to the simple test code already posted . . .

 CREATE PROCEDURE dbo.Test1
   @Switch INT = 0
 AS
 BEGIN
   SET NOCOUNT ON
   SET DATEFORMAT dmy

   IF(@Switch = 0) BEGIN
     --For simplicity assume temp table doesn't exist:
     CREATE TABLE tblTemp0
     (
       DATABASE_NAME    sysname,
       DATABASE_SIZE    int,
       REMARKS    varchar(254)
     )

     --Insert data from sp_databases into a temp table:
     INSERT INTO dbo.tblTemp0
     (
       DATABASE_NAME,
       DATABASE_SIZE,
       REMARKS
     )
     EXEC sp_databases

     --From ADP, this works OK:
     SELECT * FROM dbo.tblTemp0

     DROP TABLE dbo.tblTemp0
   END ELSE BEGIN
     CREATE TABLE tblTemp1
     (
       hpOwner VARCHAR(100),
       hpObject SysName,
       hpGrantee VARCHAR(100),
       hpGrantor VARCHAR(100),
       hpProtectType VARCHAR(100),
       hpAction VARCHAR(100),
       hpColumn VARCHAR (100)
     )

     --Insert data from sp_helprotect into a temp table:
     INSERT INTO tblTemp1
     (
       hpOwner,
       hpObject,
       hpGrantee,
       hpGrantor,
       hpProtectType,
       hpAction,
       hpColumn
     )
     EXEC sp_helprotect

     --From Access ADP, this fails with the message...
     --"stored procedure executed correctly but did not return
records";
     SELECT * FROM dbo.tblTemp1
   
     DROP TABLE dbo.tblTemp1
   END --IF
 END

It works for me when called from ADP's 'Queries' window with both 0 and 1 as input , showing me grants or database in a datasheet
view accordingly.

I recommend turning on trace and see what ADP is sending to SQL Server in your case.  Can't image why it should be different.

Wild guess: maybe your definition of sp_helprotect is doing a 'set nocount off'.

What version of SQL Server / Access?