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:
USE [GRBImportSSMA]GO/****** Object: StoredProcedure [dbo].[spMJO1st] Script Date: 7/13/2015 11:28:09 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spMJO1st] ASBEGIN -- Insert statements for procedure here SELECT NumberOfPayments, AmountOfPayments, DateTimeOfBatch from dbo.tblBatchPaymentLog order by NumberOfPaymentsEND
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.RecordsetSet rsIn2 = New ADODB.RecordsetrsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic'If rsIn2.EOF Then 'Else If rsIn2.RecordCount > 0 Then ' ' While Not rsIn2.EOF ' ' rsIn2.MoveNext Wend End IfEnd If'rsIn2.CloseSet 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?
.
Microsoft AccessMicrosoft SQL Server
Last Comment
mlcktmguy
8/22/2022 - Mon
sameer2010
You can do the similar processing. Need to make use of ADODB.Command object. Set the commandtype as adCmdStoredProc. Set the command connection to SQL Server connection. Set recorset object as commandobject.execute. Rest of the processing remains the same.
YZlat
you could use DAO.QueryDef object:
Dim qdef As DAO.QueryDefSet qdef = CurrentDb.CreateQueryDef("")qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connectqdef.SQL = "EXEC spMJO1st"qdef.Execute
sameer2010, I've never used the format your describing. Could you alter the small code loop I included to include the pertinent commands you are referencing?
YZlat, not familiar with the DAO.querydef object. Could you give an example of what the statement
"qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect"
would look like with the actual tables included. The stored procedure references a table but which table would go in the qdef,Connect command.
At the conclusion of the qdef commands you show, would I go thru my example loop replacing 'rsIn2' with 'qdef'?
If you could modify the example loop I included to use your new commands I would appreciate it.
Not having worked with either of these approaches I am having trouble visualizing the complete solution.
That code will work if you have a linked table in your Access database. You can create a linked table in access to point to an existing SQL Server table. Could be any table from what I understand, but I think you should link to dbo.tblBatchPaymentLog.
So go ahead and create a linked table from your access database and then use the name of that linked table in your code
Set adCmd = New ADODB.Command
adCmd.ActiveConnection = adCn
adCmd.CommandText = spName
adCmd.CommandType = adCmdStoredProc
'adCmd.Parameters.Refresh
'adCmd.Parameters(1).Value = 10
Set adRs = adCmd.Execute
'
If adRs.EOF Then
'
Else
' adRs.MoveLast
' adRs.MoveFirst
wkNumRecs = adRs.RecordCount
'
While Not adRs.EOF
'
wkRecsRead = wkRecsRead + 1
'
adRs.MoveNext
Wend
'
End If
'
adRs.Close
adCn.Close
'
Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub