Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run StoredProc from Access to populate local holding table 1

Status
Not open for further replies.

03Explorer

Technical User
Sep 13, 2005
304
US
Working with Stored Procedures is new for me. I have a Sproc: (snippet of code we are using)

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!
 
I typically use a Pass-Through query and a little DAO code to change the SQL of the p-t faq701-7433.
You can then use the p-t to append to another table.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Duane, what I need is a stored procedure (MS SQL SERVER) to be sent the variables and the outcome appended to a local table. The local table is used by the current user, but other users would have their own local table for viewing their dashboards.

I have not used Pass-through queries and see this as an action to change a SQL Server Query (instead of executing a Stored Procedure). I need to execute the stored procedure on the SQL server and then take the output and append it to a purged table for use in the dashboards. The SP I gave above is a snippet of the actual one.

Or am I not reading your Linked post correctly? Can I use that to execute the SP with the variables and then get the output appended to a local table?

Sorry if I am not speaking clearly.

Thanks,
Rob
 
The SQL of your pass-through would be something like:

Code:
EXEC Test_SProc_SurveysScoresandComments 'XXXXXXXXX', '12/1/2018', '1/31/2019'

You just need a little code as referenced to change the parameters. Then you can select from the p-t like you would any other query. Very little code and very efficient.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Okay, Duane, I am lost in what you are saying.

I have a local table to Access that I want to populate from the output from a stored procedure (MS SQL SERVER) that is sent three parameters.

Do I need to link to a Stored Procedure (a table within Access)?
if not, how in your code am I connecting to the SP? ODBC, direct connect, ???

Also is the p-t table the destination table for the SP output?
or what is the point of the p-t table?

I hope I am making this overly complicated but as I am reading the thread and linked thread, it's not helping me connect the dots.

Rob
 
A pass-through query has a connection property much like a linked table from SQL Server. You can run any SQL statement the server supports and it bypasses any Access overhead so it is very efficient. A ODBC Connect Str might be something like:
Code:
ODBC;Driver={SQL Server};Server=[YourServerName];Database=[YourDatabaseName];Uid=[UserLogin];Pwd=[Password]

You can look at your linked table connections if you need assistance. If your [highlight #FCE94F]INSTANCE[/highlight] on SQL Server and/or [highlight #8AE234]Port Number (#####)[/highlight] are not default, you may need something like:
Code:
ODBC;Driver={SQL Server};Server=[YourServerName\[highlight #FCE94F]Instance[/highlight],[highlight #8AE234]#####[/highlight];Database=[YourDatabaseName];Uid=[UserLogin];Pwd=[Password]

I provided the SQL view that would be in your pass-through query. The pass-through can run a stored procedure and return the results.

You use the p-t query to append to your local table.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Duane, I finally got it. I had to understand that the pass-through query needed to be established and it does not hold any structure what so ever. The VBA code redefines the back end SQL of the P-T query. Which I used SQL first and then the Stored Procedure code you gave me. Perfect.

What I found is once I created the P-T query and saved it, I had to open it to define the ODBC connection. For me, I did that once and it never prompted me again for the ODBC connection, even after changing from SQL to SP language. For my partner, I gave him the code to put into his accdb and he did the same process (I watched him via Skype) He is prompted every time to define the ODBC for the one same p-t query. Any idea why his is asking every time the p-t is viewed? (not design view).
 
Duane, we found out that when I created the P-T query for the first time, it prompted me for DSN/ODBC. After that execution, it never asked again. But when my colleague did the same, it never held on to his ODBC connect string. After a few tests on my machine, I was experiencing the same... Every time I execute the p-t it prompted for ODBC source. I found that I had to open p-t query in developer mode and open properties and manually plug in the connection string. Seems odd that I have to do that now. Before (for a few p-t queries) I didn't experience needing it for every time I view the P-T query.

Any insight on the issue?
Thanks,
Rob
 
Does your connect string actually contain the name of a DSN?

Is the connection using Windows Authentication or a login and password?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
We have a global populated DSN that uses Windows Authentication. I did not use any connection strings in my p-t query. When I created the p-t query I make in GUI, click Pass-through... now shows SQL text. Save, to get prompted for file name... When I click to view the newly saved p-t query, I am prompted for connection... This typically was a one time select and go... now it's needed every time I open the p-t query. I did open p-t query and went to properties and see the connection string is only ODBC.

Do I/we need to define the connection string when building any p-t query in the properties of the query?

The p-t query code (VBA) does not contain a connection string... or SHOULD it?
 
The P-T query is a saved query in your database and should have the connection string stored in its properties. I typically don't use a DSN so my saved connection property looks like my suggestion on 2/6. No user gets prompted for any connection or authentication.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top