Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Using ADO with Access reports

    I am using Access 2003 as a front end to a SQL Server 2005 Express database. My plan is to use ADO code for all interaction between the front end and back end. Is there any way to use an ADO recordset as the recordsource of an Access report?

    I found one example but what it did was create a local temporary table and set the report recordsource to that temp table. It doesn't actually use ADO, but uses parts of the ADO connection/recordset to create the temporary table.

    Thanks for any replies.

    BTW - Is the dbforum search feature broke? I found one post asking about it, but there was no response.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nope ... Cannot do ... Access Reports can be bound to stored procs, tables, or set programmatically ... Can't be bound to an ADO recordset.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For sub reports I will write to a temp table. Otherwise I use pass through queries.

    The source of the problem (as I understand it) is that a recordset is simply a cursor (a load of pointers to individual records) whereas reports require sets of data (for groupings and aggregations). Hopefully one day this problem will be sorted out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    If that is the case then the only thing I can think of is using the ADO recordset to create a temporary table locally in Access and base the report off of that. This is different from the example above in that the example sets up a link to the table and then bases the report off of it.

    I've seen some things on pass-through queries but that is not what I'm looking for either.

    EDIT - pootle, we were posting at the same time and I didn't see your response.

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.

    If anyone has any more opinions I'd like to hear them still.

    EDIT - another simultaneous post, with Teddy this time
    Last edited by gwgeller; 09-25-06 at 17:54.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You should be abstracting your data layer with stored procs and views server-side. You lose a good deal of the really neat funcitonality you gain by going with sql express/mssql by pulling direct recordsets. There are design and maintenance considerations for using procs and views instead of raw queries as well. If for some reason your backend changes or the logic behind a query changes but the result set is the same, you don't have to change your front end, only the proc/view.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gwgeller
    I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.
    Sounds good
    Quote Originally Posted by gwgeller
    If anyone has any more opinions I'd like to hear them still.
    I suspect you will eventually find you need a pass through one day - try writing n thousand line recordsets to local tables if you don't believe me.

    Partly because of the ADO issue I export most of my reports to Excel now (using a nifty Excel method - CopyFromRecordset). Obviously doesn't work for reports requiring formatting (such as invoices and the like) but the punters can get their hands dirty with the data in Excel and so it has been a popular move.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by gwgeller
    I think what I will do is create sprocs that will provide the data needed for the report. I'll return it to an ADO recordset in Access and then create a local Access temp table based on the recordset and then base the report on the local temp table.

    If anyone has any more opinions I'd like to hear them still.

    EDIT - another simultaneous post, with Teddy this time
    I don't see the reason for a temp table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jan 2004
    Posts
    145
    pootle, I believe you on the huge recordsets, but mine should be limited. I don't know much about pass through queries but how do you create yours? Do/can you create them dynamically or set them up in the .mdb/.adp file? If I have to store the queries in the .mdb/.adp file that is taking away from the abstraction right? I envisioned having nothing but forms, reports and modules in my front end.

    Teddy if I don't use a temp table how would my report access the data?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    All my data manipulation (crosstabs excluded) is in views and sprocs on the server. My app accesses sprocs only (via pass through queries). A pass through is just a connection and a string of unparsed text sent to the server to execute - it is just about the dummest query there can be.

    So - I use pass throughs (created dynamically - there is no other way - you need to programmatically change the SQL if you need to pass parameters) to call sprocs.

    Pass throughs do present security holes. You are passing text to the server to execute and the connection string is stored as part of the pass through properties.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    All my data manipulation blah blah blah ...
    What's up???? What happened to the audacious cuddler of flumps???? Too hoity-toity to associate with us riff-raff??? So, what brought about this change of heart ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    So, what brought about this change of heart ...
    Boredom
    Quote Originally Posted by M Owen
    Too hoity-toity to associate with us riff-raff???
    Yup
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by gwgeller
    pootle, I believe you on the huge recordsets, but mine should be limited. I don't know much about pass through queries but how do you create yours? Do/can you create them dynamically or set them up in the .mdb/.adp file? If I have to store the queries in the .mdb/.adp file that is taking away from the abstraction right? I envisioned having nothing but forms, reports and modules in my front end.

    Teddy if I don't use a temp table how would my report access the data?
    Recordsource = "EXEC sp_MyProc"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    Recordsource = "EXEC sp_MyProc"
    ADP\ ADEs only
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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