mlcktmguy
asked on
MS Access Pasthru Query to Execute SQL Stored procedure
I developing my first access application with a SQL Server back end. I've not used SQL before and I'm exploring the functionalities that come with the SQL backend. With help from EE I developed a VBA & ADO approach to execute SQL Stored Procedures from Access.
I have also created some simple pass thru queries in Access (Select field1, field2.. etc.) to retrieve information from SQL. I have read that it's also possible to execute stored procedures from a pass thru query but don't know how to do that, or if its any better/more efficient than executing them in VBA using ADO.
Is it possible to create a pass thru query that executes a stored procedure that returns values? How would that look?
What would a pass thru query to perform the same functionality as my VBA/ADO routine below look like? This one only passed parameters to a stored procedure that creates a record in a SQL Table.
Public Sub writeComment_Sproc(passedP ropertyID As Long, _
passedComment As String, _
Optional passedTaxAuthorityID As Long = 0, _
Optional passedTaxTypeID As Long = 0, _
Optional passedUserName As String = "", _
Optional passedCommentTypeID As Long = 1, _
Optional passedDate As Variant = Null)
'
wkDateTime = Now
wkUser = GimmeUserName
If Len(Trim(passedUserName)) > 0 Then
wkUser = passedUserName
Else
wkUser = GimmeUserName
End If
'
If IsValidSQLDate(passedDate) Then
wkDateTime = passedDate
End If
'
Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
'Dim adRs As ADODB.Recordset
Dim sConn As String
Const spName = "dbo.sptblCommentsAdd"
setSQLConnection
sConn = gConnection
'
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 = passedPropertyID
adCmd.Parameters(2).Value = passedComment
adCmd.Parameters(3).Value = passedTaxAuthorityID
adCmd.Parameters(4).Value = passedTaxTypeID
adCmd.Parameters(5).Value = passedCommentTypeID
adCmd.Parameters(6).Value = wkUser
adCmd.Parameters(7).Value = wkDateTime
adCmd.Execute Options:=adExecuteNoRecord s
'
'adRs.Close ' no recordset on this one
adCn.Close
'
'Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub
'///////////////////////// ////////// ////////// // Stored Procedure
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[sptblCommentsAdd] Script Date: 4/19/2017 2:13:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptblCommentsAdd]
@PropertyID int,
@Comment nvarchar(MAX) = '',
@TaxAuthorityID int = 0,
@TaxTypeID int = 0,
@CommentTypeID int = 0,
@UserAdded nvarchar(100) = '',
@DateAdded datetime = null
As
Begin
Set NOCOUNT on;
SET @DateAdded = COALESCE(@DateAdded, GETDATE())
Insert dbo.tblComments (
PropertyRecID,
TaxAuthorityID,
TaxTypeID,
CommentTypeID,
Comment,
DateAdded,
UserAdded)
Values (
@PropertyID,
@TaxAuthorityID,
@TaxTypeID,
@CommentTypeID,
@Comment,
ISNULL(@DateAdded, GETDATE()),
@UserAdded)
END
I have also created some simple pass thru queries in Access (Select field1, field2.. etc.) to retrieve information from SQL. I have read that it's also possible to execute stored procedures from a pass thru query but don't know how to do that, or if its any better/more efficient than executing them in VBA using ADO.
Is it possible to create a pass thru query that executes a stored procedure that returns values? How would that look?
What would a pass thru query to perform the same functionality as my VBA/ADO routine below look like? This one only passed parameters to a stored procedure that creates a record in a SQL Table.
Public Sub writeComment_Sproc(passedP
passedComment As String, _
Optional passedTaxAuthorityID As Long = 0, _
Optional passedTaxTypeID As Long = 0, _
Optional passedUserName As String = "", _
Optional passedCommentTypeID As Long = 1, _
Optional passedDate As Variant = Null)
'
wkDateTime = Now
wkUser = GimmeUserName
If Len(Trim(passedUserName)) > 0 Then
wkUser = passedUserName
Else
wkUser = GimmeUserName
End If
'
If IsValidSQLDate(passedDate)
wkDateTime = passedDate
End If
'
Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
'Dim adRs As ADODB.Recordset
Dim sConn As String
Const spName = "dbo.sptblCommentsAdd"
setSQLConnection
sConn = gConnection
'
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 = passedPropertyID
adCmd.Parameters(2).Value = passedComment
adCmd.Parameters(3).Value = passedTaxAuthorityID
adCmd.Parameters(4).Value = passedTaxTypeID
adCmd.Parameters(5).Value = passedCommentTypeID
adCmd.Parameters(6).Value = wkUser
adCmd.Parameters(7).Value = wkDateTime
adCmd.Execute Options:=adExecuteNoRecord
'
'adRs.Close ' no recordset on this one
adCn.Close
'
'Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub
'/////////////////////////
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[sptblCommentsAdd] Script Date: 4/19/2017 2:13:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptblCommentsAdd]
@PropertyID int,
@Comment nvarchar(MAX) = '',
@TaxAuthorityID int = 0,
@TaxTypeID int = 0,
@CommentTypeID int = 0,
@UserAdded nvarchar(100) = '',
@DateAdded datetime = null
As
Begin
Set NOCOUNT on;
SET @DateAdded = COALESCE(@DateAdded, GETDATE())
Insert dbo.tblComments (
PropertyRecID,
TaxAuthorityID,
TaxTypeID,
CommentTypeID,
Comment,
DateAdded,
UserAdded)
Values (
@PropertyID,
@TaxAuthorityID,
@TaxTypeID,
@CommentTypeID,
@Comment,
ISNULL(@DateAdded, GETDATE()),
@UserAdded)
END
ASKER
DatabaseMX: Thank you, do you have any examples of you pass thru queries calling a stored procedure?
Yes ... you are in luck. I will post in a bit ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, at this point I don't exactly understand it but I'll try to figure it out and get back to you.
Then don't EVEN think about using ADO lol !!
I figured all that out over the last week or so ... and there is a lot more to the story ... regrading 'permissions' required to run SPs and so on.
I figured all that out over the last week or so ... and there is a lot more to the story ... regrading 'permissions' required to run SPs and so on.
Here is a very tiny sample. You are in for some serious Google research ... just like I'm doing.
https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx
And don't believe everything you read ... and note that many other sites have better examples than Microsoft :-)
https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx
And don't believe everything you read ... and note that many other sites have better examples than Microsoft :-)
ASKER
Thanks very much. I was to use the code you posted (modified for my specifics) to execute the pass thru passing parameters.
I'm going to post some followup questions about this.
I'm going to post some followup questions about this.
FYI ... no need for ADO .... LOTS of misleading info about that.
VBA/DAO works just fine. I know because I am currently going through this.
"I have read that it's also possible to execute stored procedures from a pass thru query"
That is exactly what I am doing. And it way less code that ADO.
"Is it possible to create a pass thru query that executes a stored procedure that returns values? "
I'm sure it is ... haven't done that ... yet :-)