Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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?
.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.
LVL 13
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
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
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
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
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
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.