I am a long time Access user but a complete NUB to using a SQL Server back end.
From with SQL I created my first Stored Procedure called 'dbo.spMJO1st'. This is it:
/****** Object: StoredProcedure [dbo].[spMJO1st] Script Date: 7/13/2015 11:28:09 AM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spMJO1st]
-- Insert statements for procedure here
SELECT NumberOfPayments, AmountOfPayments, DateTimeOfBatch
order by NumberOfPayments
There are about 90 records in the table.
As you can see, It is very simple. It select 3 fields from a SQL table and puts them in order by one of the fields. At least I think it does.
I have a routine in my Access app that reads a table and goes thru all entries. for the purposes of this question, that's all it does.
This is the processing loop:
selectString = " Select * From tblBatchPaymentLog "
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsIn2.EOF Then
If rsIn2.RecordCount > 0 Then
While Not rsIn2.EOF
Set rsIn2 = Nothing
I would like to revise the processing loop to use the result set from the stored procedure instead of the Access table.
How do I change the ADO commands to accomplish this?