0

Update: Does anyone know what needs to be done to the reports in Access to make them prompt for parameter values as they would in an .mdb but retrieve the data from a SQL Server stored procedure? To quote my comment on @OverMind's answer below:

As to the VBA code, I am somewhat familiar with VBA but to be more specific, that code opens a report programmatically whereas I would like the code to run when the user opens a report and have it prompt for the parameters like before. I tried using the report's Open event and setting its record source to the qdf from that code, but that did not work; also in the example the parameter is hard-coded.

I am in the process of converting an old MS Access database to SQL Server, so naturally I started off by using the upsizing wizard. Unfortunately, out of about 80 queries, the upsizing wizard was only able to convert 5 or so, because the rest use parameters (the kind where Access prompts you to fill in the parameter value when you run the query), and there are also reports that depend on those queries.

Is there any way to automatically convert those queries to stored procedures in SQL Server or does that have to be done manually? And once they are converted, how can I configure the reports in Access to use those stored procedures as their data source and prompt the user to fill in the parameter values like before? The client does not have the budget to convert the reports to SQL Server reports.

From searching around the web, I did see some people reporting that they were able to convert such queries to SQL Server stored procedures automatically (e.g. the comments at the bottom of http://sqlmag.com/database-development/beyond-upsizing-wizard), but unfortunately I could not find any details on this. And as to the reports, I found this but I'm not sure where to put that code.

Improve this question
0

1 Answer 1

Reset to default
1

See my answer here

There is no "tool" that can be used to do such things. The parameter values are something I'm not sure of. You're going to have to find a way to get them into your query one way or another, if they're necessary. This will be up to you. (Some front end application or UI I'm assuming?) This link will provide you with some valuable information on Access queries to SQL Server SPs.

What confuses me in your question is that you don't specify how you're going to access the data. Is it through an Access front end? In the documentation it even states one of the ways to Upsize is :

Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).

If any MS Access SQL is to be converted to T-SQL, and there are differences in formatting, you will need to correct those. They should not be TERRIBLY different, but there are differences that will need to be addressed. The first link will address some of those differences. (different functions, etc)

The code you linked to run an Access report from a SQL Server stored procedure written by Gord Thompson needs to be placed in Access VBA. Inside access, at the top ribbon, click on Database Tools, to the very left, click Visual Basic. You will need to create an event handler to execute this code. See here for help on Pass-Through queries. Here's an MSDN link on them also.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks. Yes, the data will be accessed through an Access front-end - the client would like to be able to continue using the existing queries, forms and reports in Access (the reason for the upgrade is primarily related to performance/multi-user problems and because we want to create a web form on an external web server that can save data to the database).
As to the VBA code, I am somewhat familiar with VBA but to be more specific, that code opens a report programmatically whereas I would like the code to run when the user opens a report and have it prompt for the parameters like before. I tried using the report's Open event and setting its record source to the qdf from that code, but that did not work; also in the example the parameter is hard-coded.
You can create a form that opens when the report opens (this doesn't make sense to me, but oh well), have textboxes on that form, capture the values from the textboxes into variables in VBA, use those as your @Parameters in your SP.
It doesn't make sense to you why there need to be fill-in parameters? It's for things like a starting and ending date for the report. Anyway, it sounds like a form opening when the report opens would work; I will give that a try, thanks.
@MattBrowne certainly, I agree, however I think that, typically, should be triggered by a button click, or something. That way, the parameters are already entered and nothing goofy seems to be happening to the user. I am in no way insulting you, it's just not as customary. I hope it worked, regardless.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.