×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Run StoredProc from Access to populate local holding table

Run StoredProc from Access to populate local holding table

Run StoredProc from Access to populate local holding table

(OP)
Working with Stored Procedures is new for me. I have a Sproc: (snippet of code we are using)

CODE --> SPROC

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 --> VBA

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!

RE: Run StoredProc from Access to populate local holding table

(OP)
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

RE: Run StoredProc from Access to populate local holding table

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

CODE --> Passthrough

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

RE: Run StoredProc from Access to populate local holding table

(OP)
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

RE: Run StoredProc from Access to populate local holding table

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 --> ODBCConnect

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 INSTANCE on SQL Server and/or Port Number (#####) are not default, you may need something like:

CODE --> ODBCConnect

ODBC;Driver={SQL Server};Server=[YourServerName\Instance,#####;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

RE: Run StoredProc from Access to populate local holding table

(OP)
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).

RE: Run StoredProc from Access to populate local holding table

Does your ODBC connect include a DSN name?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run StoredProc from Access to populate local holding table

(OP)
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

RE: Run StoredProc from Access to populate local holding table

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

RE: Run StoredProc from Access to populate local holding table

(OP)
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?

RE: Run StoredProc from Access to populate local holding table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close