Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating 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?
.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial