Generally to use a SELECT stored procedure, you would do this on the Access side:
Set cn = New ADODB.Connection
'Create a new command object
Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn
'Specify the stored procedure to run
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "usp_MySelectProcedure"
'Create the parameter if needed
Set prmRecID = cmdCommand.CreateParameter("@RecordID", adInteger, adParamInput)
prmRecID.Value = lRecordID 'Long
Set rs = cmdCommand.Execute
Now you can use the recordset as you normally would.
On the server side, the stored procedure might look like:
ALTER procedure [dbo].[usp_MySelectProcedure]
SELECT CID, Customer, A1, APC,rep
WHERE CID = @RecordID
Did that help? Of course you wouldn't need the parameter.