ADO data control with Access Reports

[rbowes]

I'm using Office 2000 Developer and I have a front end developed in Access and a Back end on SQL Server 7. I use no linked tables because the application summerizes the data and my experience has been that using ADO recsets and commands with commandtext and stored procs work much faster than using DAO and sending the information through Jet to SQL Server. Forms have a recordset property that can be set using VB, but reports do not. I have come up with some really cool ways to use arrays as record sources for reports, but they are limited. I was wondering if anyone has come up with a way to use either an ADO Recordset object or ADO data control to populate the controls on a report.

 

[SteveR77]

You should be able to set the ADORecordset object to be the data source for your report and have it work.

I can do a mock up of this and give you a precise answer, but I am fairly confident that this should work.

Steve

 

[rbowes]

I tried that but get the error message that DataSource is a Method or Data member that is not found. When I set the Record Source of the report to the recordset I get a Type Mismatch error.

If there is something that I'm not referencing please let me know.

Bob

 

[vr]

I use pass through queries to populate my reports.

 

[rbowes]

That usually is a good idea and I've tried it before, but this db is so normalized that to get all of the information for most of my reports can mean linking as many as 6 tables with some self-referencing tables to boot. Even pass-through queries can take a long time. One suggestion I got recently was to use an ADO command object to "CREATE TABLE..." and then populate the temp table with an ADO recordset by looping through it and using .addnew and .update batch. It still takes some time, but I get the flexiblity of creating a SQL statement in the Record Source property of the report that moves pretty fast. I would be nice if the next version of Access has report objects with a recordset property.

Thanks for your input!

Bob