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.

Nope ... Cannot do ... Access Reports can be bound to stored procs, tables, or set programmatically ... Can't be bound to an ADO recordset.

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.

pootle flump ur codings are working excelent.

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.

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

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.

Sounds good
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.

pootle flump ur codings are working excelent.

I don't see the reason for a temp table?

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?

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.

pootle flump ur codings are working excelent.

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 ...

Boredom
Yup

pootle flump ur codings are working excelent.

Refers to some pointless nonsense in the 'Coral:
http://www.dbforums.com/showthread.p...89246&page=330

Concluding here:
http://www.dbforums.com/showthread.p...89246&page=331

pootle flump ur codings are working excelent.

Recordsource = "EXEC sp_MyProc"

ADP\ ADEs only

pootle flump ur codings are working excelent.