Report to display data from sql serv.

[Guest]

I’m using Access for the front-end and SQL Server at the backend. In Access you can print a report based in the currently opened record in a form by using [Forms]![Current_Form]![Current_Record_ID

How would I do this in Access with SQL Server? Do I use a stored procedure? I’m all new to this stuff.

Can someone give an example or something? Maybe you know of a good website with tutorials

Any help is greatly appreciated

 

[Newbie]

How are you populating your form? Are you using ADO?
I am new to this as well but I create a recordset based on a stored
procedure and add a;; the records to a temporary table within access. The
form is then based on the temporary table. normal access code should then
work

HTH

I'm using Access for the front-end and SQL Server at the backend. In

Access you can print a report based in the currently opened record in a form
by using [Forms]![Current_Form]![Current_Record_ID]

How would I do this in Access with SQL Server? Do I use a stored

procedure? I'm all new to this stuff.

 

[Newbie]

If you are using an access project then I believe it is the same as if it
were an access database.
Except a view is a query and a stored procedure (my understanding of it) is
another kind of query that helps speed up processing because once it has
been run it holds the execution plan and therefore doesn't have to be
recompiled each time.

I set up a form that show all records with a command button that prints a
report just for the current record.

This is what I had on the OnClick event of the command button

DoCmd.OpenReport "tstview", acViewPreview, , "Grn = '" & Me.Grn & "'"

HTH

I'm not using recordsets. My forms are driven by the tables in the

database windows in Access on SQL Server. If I click on the tables in the
database window see my tables. (e.g. "customers(dbo)")

I know my way around Access. But i feel clueless with this stuff.
I want to learn but the books I bought are not for the novice. I still

don't understand what a instance is? Those books aren't any help.I need some
serious help!

 

[SA]

This is what I had on the OnClick event of the command button

DoCmd.OpenReport "tstview", acViewPreview, , "Grn = '" & Me.Grn & "'"

Yes you can do it just that way with ADP's (where Access is connected
directly to the Server.) using a table or a View from SQL as the record
source (i.e. a View is query which has no parameters, no grouping and does
use functions like Access' IIF statements.)

If you have a query which requires parameters, group by elements, functions
such as sum() avg() , IIF (case statements in SQL Server) scenarios etc.
then you need to create a Stored Procedure in SQL Server and use that as
the record source for your report. With stored procedures (known as
"sprocs") you use the Input parameters property of an Access report if the
parameters are statically sourced (e.g. they are based on a form's specific
field values. To see an example of this, load the NorthwindCS ADP example
database that comes with Access and look at the Sales by Year example
report.)

The even for simple filtered select queries, the advantage of stored
procedures over Views where you filter the records like example shown above
using the SQL where condition, expecially with large sets of data, is that a
stored procedure will run entirely on the server and return the target
(smaller, focused) recordset over the network to your user application. If
you use a View or Table as the record source then you are returning all
records and Access is dynamically filtering those, which is much, much
slower when dealing with large sets of records pulled over a network. What
you do with a stored procedure is to write a query which expects parameters
such GRN above, and then supply those parameters at run time. Creating SQL
stored procedures is much much easier in Access 2002 and 2003 than in Access
2000, (where you needed to basically write Transact SQL,) because those
versions have better visual UI tools for doing so. If you are unlucky
enough to by trying to create your ADP in Access 2000, then run, don't walk
to your nearest retailer and upgrade to 2002 or 2003.

If you have a stored procedure that expects parameters as the record source
for your report, and you are using Access 2002 or 2003 (this won't work in
Access 2000,) then you can set the recordsource for your report dynamically
at run time supplying the parameters in code by using the Exec command. As
an example: for the same Sale By Year report, copy it, eliminate the current
recordsource and input parameters and in the Report's On Open event add code
like this:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

This uses SQL Server's Exec command to run a stored procedure, which is
pretty simple to do.

Starting out with ADPs you might want to buy the Access Developer's Handbook
Volume 2, published by Sybex, which covers Access ADP database development
reasonably well. Its written from an Access developer's perspective rather
than from a SQL Server and Tranact SQL technical perspective. Once you've
got the basics down with that book, you would be well advised to buy a good
SQL Server book, because there are lots and lots of optimization techneques
for getting and manipulating data in SQL that the average Access developer
doesn't think about or know and that would help you with indexing, writing
effective stored procedures, functions and other items that make SQL really
a great db. In may opinion, the Wrox press books, including anything
written by Robert Vieria are generally quite good and very accessable reads.

HTH