Executing a Stored Procedure from SQL Server to Access 2013

[SBWolfe_1954]

I need to execute a stored procedure from SQL Server into Access 2013 and add the results to a temp table. I will then be using the data in the temp table to perform multiple calculations and prepare different reports.

I thought using a Pass through Query would be the best way. The following is the start of that query:

ODBC;DSN=SQLServer2012;Description=dbo rpt tables in WES_DW;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=WES_DW;

EXEC [dbo].[usp_Usage_Report]
@Startdate = '1/1/2017',
@Enddate = '6/30/2017',
@ShipToName = '',
@billtoName = '',
@PriceLine = '',
@SellGroup1 = '',
@ProductIDs = '',
@PartNumber = '',
@PriceBranch = 'ALL'

This fails because it does not recognize my connection on the first line. It keeps popping up to be entered. If I try to give it the actual connection it still fails.

I need to execute the procedure and add the results to a temp table, so I am assuming additional lines need to be written.. I have googled this but have a lot of conflicting accounts on how to do it. I want to keep it simple because this is the only part coming over from SQL Server. The remainder of the procedure will have me working in Access on the temp table data. Once I have this figured out, I can use it as a standard for other sets of data I am working with. They will all come over from SQL Server in the same manner, just coming from other data sources.

Any help I can receive would be greatly appreciated.

 

[xenou]

What's the App supposed to mean in that connection string?
APP=Microsoft Office 2013

Description looks weird to me too:
Description=dbo rpt tables in WES_DW


edit:
Note that you should test your connection string with a simple query:
select top (1) * from dbo.SomeTable

 

[james_lankford]

create a pass through query
open the query in sql view
open the property sheet


put this in the
ODBC Connecton Str
field


ODBC;Description=my_description;DRIVER=SQL Server;SERVER=name_of_computer_sql_server_is_on--or--ip_address;DATABASE=name_of_database;UID=user_id;PWD=password


change
my_description
name_of_computer_sql_server_is_on--or--ip_address
name_of_database
user_id
pasword
to the values that they should be




but this should not be changed
DRIVER=SQL Server


then test it out with a simple


exec sp_who 'some_user_name'


if that works then save that query --- let's say you name it sp_query


then create a regular access query that uses sp_query as its source and does a "make table"


once you run that 2nd query it should make a new table with the results of sp_who in it


now go back to sp_query and change it to
EXEC [dbo].[usp_Usage_Report] '1/1/2017', '6/30/2017', '', '', '', '', '', '', 'ALL'


I think that should work

 

[SBWolfe_1954]

This worked perfectly. Thank you. However, the problem I have now is passing variables to the start and end date of the stored procedure. A user will enter in a start and end date on a form and then execute the stored procedure. However, I receive this message when it goes to "insert" the record, "Conversion failed when converting date & or time from character string #241 ". This error is on the SQL Server side. The start date and end date on the form are text boxes. The start and end date in the stored procedure is varchar. How do I made the two fields compatible between Access and SQL Server?

 

[xenou]

You should be passing the parameters as in the example from james.
EXEC [dbo].[usp_Usage_Report] '1/1/2017', '6/30/2017', '', '', '', '', '', '', 'ALL'
If I were to hazard a guess, you haven't formatted the command correctly. Maybe you didn't put single quotes around your dates, for instance. For debugging purposes, you should look at the string command you are passing to sql server - it should be one whole entire and fully compatible sql server command.

 

[SBWolfe_1954]

Yes it works when I pass the actual value such as '1/1/2017' (start date) and '6/30/2017' (end date) However, because the stored procedure is going to be called from a form, I need to pass the value from the field in the form. (Example: Me.TxtStartDate). When I try to do this, I receive the error I mentioned. When I traced what was happening, I am not passing a value, I am passing Me.TxtStartDate as the value. I tried Me.TxtStartDate.Value but the same thing happened. It is not recognizing the value within the parameter.

 

[xenou]

You have to figure out *exactly* what you are passing - and it will be a string (there is no other way to send a pass through query to SQL Server except as a sql command text. So ... when you say you pass Me.TxtStartDate how did you do that? When you passed Me.TxtStartDate.Value, how did you do that? This will determine what it is that you are ultimately spitting out and giving to SQL Server.

 

[SBWolfe_1954]

I am trying to pass the parameter in the pass through query, which I now believe is wrong. I have a string that creates the query that executes the pass through query and adds the data to the temp table. Is it here where I want to pass the parameters using a WHERE statement?

 

[xenou]

Hi,
This seems to be unnecessarily confusing. You said you are creating a string that executes the pass through query -- that is correct. But what is the string? Therein lies the rub: we need to see that string and make sure it is correct. You want to pass the "parameters" exactly as in the example from James (or something very like that): EXEC dbo.usp_Usage_Report '1/1/2017', '6/30/2017', '', '', '', '', '', '', 'ALL'

Note that for clarification we can also say that the string you "pass through" should be exactly the same as a SQL command text that you would run in SQL Server - the "pass through" query will literally pass along the string to SQL Server and SQL server will run it "as is". For this reason, you can also test you command text by running it in SQL Server Management Studio (if you have access).