How to generate a report from ADO recordset?

[marks2007]

Hi,

I use use SQL select statement with a table and generate a recordset with ADO.

I want to show the result on a Access report,Please let me know how
to use report show the result from recordset with VBA code.

Thanks for your help

Mark

 

[Oldsoftboss]

How are you gathering the sql

In a module...
Public strSQL as String

Then construct the sql
strSQL = "Select Blah Blah Blah"

In the On_Open event of the report:

Me.RecordSource = strSQL

HTH

Dave

 

[GolferGuy]

Mark, here is an article that explains how to use an ADO, or DAO recordset with a form. I have not tried it, but I assume the same technique will work with a report. If you do try this, please post back and let us all know if it works or not. Thanks!
http://support.microsoft.com/kb/281998

 

[marks2007]

Hi Vic,

I do the test but I get a error message like following

'This feature is not available in MDB,RUN TIME '2935'

Do I have to convert the mdb to adp?I do not know how to do that.Please give me a hit.

Thanks for help

Mark

 

[RoyVidar]

The hint is that the message is true - you cannot assign an ADO recordset to a report in mdb files, only ADPs.

But I don't think I'd use that as the reason to switch to ADP. The impression I'm getting by MS lack of efforts with the development of the product, is that it is on it's way out. I wouldn't be surprised if it wasn't supported anymore in the next version.

I think I'd consider lot of other stuff before switching to ADP (or perhaps use ADP only for the reporting?), even temp tables.

 

[GolferGuy]

I use use SQL select statement with a table and generate a recordset with ADO.

Why can't you just use the SQL select statement the way it is as the RecordSource for the report? You don't have to make a recordset with ADO or DAO to see those same records. The SQL select statement should work fine as the actual RecordSource for the report.

 

[marks2007]

I select a view (not table) now and want to show that in the report.thanks

 

[RoyVidar]

I don't understand.

If the object you wish to base the report on, are within the database container, you can assign the name of the query or table to the recordsource of the report.

If you by view mean a SQL server view, then you can link to a SQL Server view, then assign the view (typically some dbo_SomeName, thingie), as the recordsource of the report, or go through creating a Access/Jet query on the view, and assign that to the recordsource.

More info?

 

[marks2007]

I can not link table to SQL Server view so I can not see them in recordsource.

How to link table to view?Thanks for help.

Mark

 

[RoyVidar]

I can link to SQL Server views here on my setup. Here, this also seems possible http://www.access-programmers.co.uk/forums/showthread.php?t=108763. You should be able to do so through the "normal" procedure (File | Get External Data | Link ...), and it should appear as if it was a table, at least if you have permission to do such.

But I'm not sure I understand neither the situation, nor what you wish to achieve.