Embarrassing Access Question: Format SP results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Embarrassing Access Question: Format SP results

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-11-21 : 17:01:56

Ok, this will be my first foray into using MS Access. I admit I have something of a prejudice against it. However, I think I need it now.

I have a stored proecure that outputs a bunch of recordsets. Each recordset is a different type of user data. I need Access to make a pretty report where each one of the recordsets is formatted nicely and appropriately for its kind of content.

Now, I know I can't just ask "Help, someone teach me access," so I'm not.

Help! Can someone point me to a HOWTO for this kind of thing, or a good access <-> SQL server SP tutorial, and/or a good access reporting tutorial?

The first big obstacle I see is the first step: How in the world do I call a SQL server SP from access? I don't want to (and can't) copy the data down into an access DB.

Thanks for any and all advice. I never thought I'd see the day when I needed to start using Access.

Cheers
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-21 : 17:16:49

I am one of the few who uses Access alot with SQL server and I love they way they work together. Access actually can do some very powerful reporting, and by linking to tables and views in SQL server, you can create extremely fast ad-hoc reports and queries that would take quite a bit of time to do in SQL using the query analyzer. Access is actually quite good at sending GROUP BY's and criteria to the server and retreiving as few records as possible ... and I'm only using Access 97 !!

SO, don't be embarrased to be using Access. I'll take on the challenge of any SQL guru who doesn't believe incorporating Access and SQL is a great way to stay productive.

Anyway --

You can create something called a "Pass through" query in Access that can link to any ODBC datasource. Look it up in help. YOu just type in your transact-SQL statement, and give it an ODBC datasource, and tell it that this query Returns Records in the query's properties. Then, you can design a report based on the query you just created.

For returning multple tables, I would use multiple stored procs, one per table. Then use 1 big report with multiple sub-reports. Its much easier.

BUt what is probably the easiest of all is to create VIEWS of your data, and then you can use the JET engine to combine/join/filter/etc the results of your views into great looking fast reports.

To create a pass-through query, create a new query in design view. Then, choose "Query"->"SQL Specific"->"Pass Through". You will get a blank screen to type your statements. Type your T-SQL. Then choose the queries properties and edit the ODBC connect string.

TO link to tables or views in a SQL server, from the "Tables" screen choose "New" then "Link Table". Then choose your datasource and all of SQL's views and tables will be available. You can then make use of those views and tables in your access DB to create reports.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-21 : 18:29:38

Use access 2k and create an access data project to an existing data source. Choose your sql server database and voila!!! you got the benefits of access forms, reports, macros, vba and also sql server tables, views, sp's and triggers.

As far as creating reports it's pretty basic you go to properties add a recordsource to your report then add text fields which have fields of the recordsource. As far as layout it's up to your imagination.

You may also want to have forms which allow the users to set criteria and are then passed to the stored procedure.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-21 : 20:21:53

aiken -

First question - using DAO or ADO? It kind of makes a big difference as to how you might go about it. Since you're a newbie to the dark underworld of MS Access, I'll assume DAO 3.6.

Create a database (mdb) , create a link table (New-> Link Table) etc

You get to the "code" window for an object (like a form) by pressing ALT-F11.

Now here's some code...

Public Function fnExec(strSQL As String) As Boolean
Dim db As Database
Dim qdf As QueryDef

'Now update in database
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.ReturnsRecords = False
qdf.Connect = db.TableDefs("TABLE1").Connect
qdf.SQL = strSQL
qdf.Execute

Set qdf = Nothing
Set db = Nothing

fnExec = True

Exit_fnExec:
Exit Function

Err_fnExec:
fnExec = False
Resume Exit_fnExec
End Function

Function creates a temporary querydef "" to execute whatever SQL is passed. Rather than specifying the connection string manually, the function "copies" the connection string from a linked table in your access database - used purely for the connection string and no other reason.

Whether you're using DAO or ADO, you're probably going to find it easiest to store the result set from your SP before generating the report - rather than trying to create the report "on the fly" from the SP recordset, and so you may want to create your SP to write the results out to a table in SQL Server, and then make that "result" table the table you link to.

Ie, on the form where the user will "click the button to get the report" your code is something like:

Private Sub cmdReport_Click()
Dim strDate As String

On Error GoTo Err_cmdReport_Click

'defaultdate
strDate = Format(DateAdd("m", -3, Now()), "dd mmm yyyy")

'Get New Totals from SQL Pass-through - server is quicker to do lots of calcs
If fnExec("EXEC spREPORT_BLA '01-May-2001'") Then
DoCmd.OpenReport "rptBLA"
Else
Err.Raise 700, , "Database Error"
End If

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdUnclaimedTasks_Click
End Sub

enough for now - ask more of you want this approach

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 11/21/2002 20:23:52

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-21 : 21:14:30

All good suggestions but really save your self some time and use access 2000/2002 adp (access data project). You don't have to code anything besides setting the stored procedure or view in the reports record source.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-21 : 21:21:22

quote:

All good suggestions but really save your self some time and use access 2000/2002 adp (access data project). You don't have to code anything besides setting the stored procedure or view in the reports record source.

Wow - learn a new thing everyday - hav to start using Access 2000.

Thanks Valter

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-21 : 21:23:15

Just one more thing to pass parameters use the input parameters property to learn how to set that up press F1 while in that field.

My pleasure rrb. One day while I was trying to upgrade an access 97 to sql 2000 using the upsizing wizard I stumbled across adps. I upgraded access 97 to 2000 first and still the upsizing tools were not working, finally upgraded to access xp and the upsizing wizard worked pretty well. Next I needed a way to port the interface and that's when I stumbled across adp's. Also access xp has access data pages which allow you to create web forms, reports and charts which are dynamic. They use activex components that get installed with access xp.

One more thing you get the benefits of sql security instead of mdw which is easily decrypted and slows down your system.

Edited by - ValterBorges on 11/21/2002 21:33:10

Edited by - ValterBorges on 11/21/2002 21:36:05

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-11-22 : 15:11:23

Thanks for the help, everyone... it's definitely given me a jump start.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources