Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report from SQL Procedure (Access 2000)

    Sorry if this was asked recently:

    Is there any way to make a Sql Server (7) stored procedure the recordsource for an access report? My recordsource was an access query which was a crosstab based on a union of several queries...somewhat mickey mouse.

    I was able to create a stored procedure which does all the work of the queries. In design mode, I changed the recordsource of the report to the stored procedure name. Two things happened: 1) I got a dialog which asked for a password, and 2) My Report.Open code, which does some massaging of data, was able to recognize the recordsource, and iterate through it. However, when the report open finished, I got an error saying Access couldn't locate the recordsource. What's up with that? Any ideas? If this is un-solvable, I suppose I could have the recordsource be a Sql passthrough query, thus fooling access into using the stored procedure after all.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report from SQL Procedure (Access 2000)

    You can base a SQL Server View on the stored procedure and you should be able to link to the view just as you would a table.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report from SQL Procedure (Access 2000)

    To amplify a bit, SQL stored procedures don't return any values in general, but are more like Action queries in Access. Views on the other hand behave very much like Access queries, but usually are quite a bit faster than a query using several ODBC linked tables. Hope this helps.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report from SQL Procedure (Access 2000)

    I'm not quite sure I understand your reply, as I've been using stored procedures to return recordsets for a while, and have used them in the past as sources for Crystal Reports. In my Access project I can use the stored procedure name as a Source argument when opening an ADO recordset.

    That having been said, I still agree with you that a view would probably be better. I'm told that a view "looks" like a table to Access. Is that true?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report from SQL Procedure (Access 2000)

    Yes, for all intents and purposes, a View is the same as a table to Access. In fact, you can create an ODBC link to Views in an Access database just as you would to tables.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •