Using SQL Stored Procedure From Access VB Code

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spMJO1st] 
AS

BEGIN


    -- Insert statements for procedure here
	SELECT NumberOfPayments, AmountOfPayments, DateTimeOfBatch
	from dbo.tblBatchPaymentLog
	order by NumberOfPayments
END

Open in new window


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
    '
Else
    If rsIn2.RecordCount > 0 Then
        '
        '
        While Not rsIn2.EOF
            '
            '
            rsIn2.MoveNext
        Wend
            
    End If
End If
'
rsIn2.Close
Set rsIn2 = Nothing

Open in new window


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?

.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sameer2010Commented:
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.
YZlatCommented:
you could use DAO.QueryDef  object:

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect
qdef.SQL = "EXEC spMJO1st"
qdef.Execute

Open in new window

mlcktmguyAuthor Commented:
Thanks for the responses.

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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
YZlatCommented:
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
sameer2010Commented:
Something like the following and replace data source, user id, password. PS: I have not tested this, so might need minor changes. 
'
Dim rsIn2 As new ADODB.Recordset
Dim cmd as new ADODB.Command 
Dim conn as new ADODB.Connection
conn.connectionstring = "Provider=sqloledb;Data Source=YourServerName;Initial Catalog=GRBImportSSMA;User ID=YourUser;Password=YourPassword;"
cmd.ActiveConnection = conn 
cmd.CommandType = adCmdStoredProc 
cmd.CommandText = "spMJO1st" 
cmd.CommandTimeout = 0
conn.open
rsIn2.open cmd.Execute 


'
If rsIn2.EOF Then
    '
Else
    If rsIn2.RecordCount > 0 Then
        '
        '
        While Not rsIn2.EOF
            '
            '
            rsIn2.MoveNext
        Wend
            
    End If
End If
'
rsIn2.Close
Set rsIn2 = Nothing
conn.Close 
 
Set conn = Nothing 
Set cmd = Nothing

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Here's the final working version
Public Sub StoredProcTest()

Dim wkRecsRead As Long
Dim wkNumRecs As Long

Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
Dim adRs As ADODB.Recordset
Dim sConn As String

Const spName = "dbo.spMJO1st"

sConn = "DRIVER=ODBC Driver 11 for SQL Server;SERVER=MIKE-PC\WIN71SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=GRBImportSSMA;"

Set adCn = New ADODB.Connection
adCn.Open sConn

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.