Access 2k query conversion to Access 2k adp(SQL Server)

[WB]

I am using access to generate some reports on data that has been recently
converted from Jet to SQL Server.

The previous reports were generated using queries. The criteria was filled
in (Beginning Date, Ending Date, StoreId) using a form that the user entered
the preceeding information.

I am now trying to duplicate this report in the adp version which connects
to the SQL Server. It doesn't seem to allow me the same user interface that
allows the user to enter the criteria on a form and then the query gets the
information from the form.

Could someone clarify why this isn't working and how I can get it working?

WB

 

[Tom Ellison]

Dear WB:

An ADP does not allow you to embed references to controls on forms
within MSDE / SQL Server Views, Stored Procedures, or Functions.

You can replace this functionality by passing the values as parameters
to Stored Procedures or Functions, or by writing the code
"dynamically" in which you place the value from the control into the
SQL and then submit the SQL to the database server.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[WB]

Thank you, do you know where I can get more information on how to do this.
My report is complex with subreports and inline calculations. It appears
that I am going to have a difficult time converting the entire report.

WB

 

[Baisong Wei[MSFT]]

Hi WB,

Happy New Year and thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.

You question might be, how to transfer the parameters to the SQL Server so
that you can have a report to show the specific records between the start
date and end date as parameters as you are working with ACCESS,right?

You could use the stored procedure with parameters in SQL Server.
First you should create a stored procedure with parameters in the database
which you want to get record from like this ( you could run lin the SQL
Server Query Analyzer):

use Table_Name
go
Create proc Your_SP_Name
@StartDate datetime, @EndDate datetime
as
select .... From TableName where TimeFiled Between @StartDate and EndDate

Then, create you adp and the datasource of this adp would be connection to
the database you want to get record from in your SQL Server. Choose the
stored procedure from the Field List and finish other part in you ADP file.
When view it, there will appear a window for you to input the parameter
startDate and EndDate, you can check if the stored procedure return the
right record ( also, this checking process could be done by execute 'exec
Your_SP_Name startdate, enddate' (these two parameter should be in a valid
datetime format) in your Query Analyzer). Then save you ADP page. Save all
and exit the ACCESS. When double click the adp you saved in you disk, the
windows will appear again asking you to enter the parameters, and then it
will generate a report according to the parameter you entered.

For detailed information on how to create a stored procedure and how to
create a stored procedure using parameters, please search these part in
the SQL Server Books Online:
1) "CREATE PROCEDURE"
2) "Programming stored procedures"

Hope this information helpful in solving your problem. If you still have
questions, please feel free to post new message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support

 

[Dejan]

You can abandon ADP and do all the work as usually. Use
ODBC connection to the SQL database and link (attach) SQL
tables or views to the Access front end. Much easier than
writing stored procedures, and much easier to re-use
whatever forms, reports and program code you have. With
standard Access solutions (non ADP) forms and reports are
easily bound to the data sources. Not so with ADP.

 

[WB]

yes, this worked perfectly Thank you

You can abandon ADP and do all the work as usually. Use
ODBC connection to the SQL database and link (attach) SQL
tables or views to the Access front end. Much easier than
writing stored procedures, and much easier to re-use
whatever forms, reports and program code you have. With
standard Access solutions (non ADP) forms and reports are
easily bound to the data sources. Not so with ADP.