asked on 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:
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:
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?
.
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
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
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
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.
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
ASKER
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.
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
So go ahead and create a linked table from your access database and then use the name of that linked table in your code
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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\WIN7
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
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
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\WIN7
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
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