03Explorer
Technical User
Working with Stored Procedures is new for me. I have a Sproc: (snippet of code we are using)
Here is what I have compiled so far for ADO VBA to access the StoredProcedure (Above) by passing three parameters. The output will be needed to populate an existing local (temp/holding) table.
I have the code working up to the line where it is trying to populate @associate. The error message I am working to get past is: Type Mismatch.
I need to preface (again) I am new to working with Stored Procedures and also how to access/use them from VBA. I've been googling options on how to make it work (a) function the Stored Procedure (b) populate the output into a local temp table
Thanks!
Code:
CREATE PROCEDURE [dbo].[Test_SProc_SurveysScoresandComments]
@Associate nvarchar(9),
@PeriodBegin date,
@PeriodEnd date
AS
/*
DECLARE @Associate nvarchar(9)
DECLARE @PeriodBegin date
DECLARE @PeriodEnd date
SET @Associate = 'XXXXXXXXX'
SET @PeriodBegin = '2017-12-01' -- '2016-12-01'
SET @PeriodEnd = '2018-11-30' -- '2017-11-30'
*/
select sc.ChildID,
am.PreferredLastName,
am.PreferredFirstName,
PracticeID,
ShowPracticeID,
RoleID,
ServiceTypeID,
SkillID
into #1a_Scores
from tblSurveysCompleted sc
left join AssociateMaster am ON sc.ChildID = am.AssociateID
where sc.ChildID = @Associate
and sc.SurveyCreatedDate between @PeriodBegin and @PeriodEnd
group by sc.ChildID,
am.PreferredLastName,
am.PreferredFirstName,
sc.PracticeID,
sc.ShowPracticeID,
sc.RoleID,
sc.ServiceTypeID,
sc.SkillID
Here is what I have compiled so far for ADO VBA to access the StoredProcedure (Above) by passing three parameters. The output will be needed to populate an existing local (temp/holding) table.
Code:
Public Sub StoredProc_Testing()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
' Dim adString As Variant
Dim strAssociate As Variant
Dim dtPeriodBegin As Date
Dim dtPeriodEnd As Date
strAssociate = "XXXXXXXXX"
dtPeriodBegin = #12/1/2018#
dtPeriodEnd = #1/31/2019#
Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER=SQL Server;SERVER=SCA-DET-SQL1;DATABASE=SCA_Perform;Trusted_Connection=YES;"
cn.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "dbo.Test_SProc_SurveysandComments"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("@Associate", strAssociate, adParamInput)
.Parameters.Append prm
Set prm = .CreateParameter("@PeriodBegin", dtPeriodBegin, adParamInput)
.Parameters.Append prm
Set prm = .CreateParameter("@PeriodEnd", dtPeriodEnd, adParamInput)
.Parameters.Append prm
cmd.Execute
prm.Value = "Tom"
End With
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open cmd
End With
' Set data from recordset to populate into a local (Temp/Holding)table
' Set Me!lstJobQuickSearch.Recordset = rs
' Me.lstJobQuickSearch.Requery
Set prm = Nothing
Set cmd = Nothing
End Sub
I have the code working up to the line where it is trying to populate @associate. The error message I am working to get past is: Type Mismatch.
I need to preface (again) I am new to working with Stored Procedures and also how to access/use them from VBA. I've been googling options on how to make it work (a) function the Stored Procedure (b) populate the output into a local temp table
Thanks!