Example of dumping a Recordset in a Report

[spwiz]

HI

This is my first posting so be kind. I normally deal with ASP and SQL but some clever spark wants a report generated in ACCESS....

What i need is an example of how i can use a returned recordset to an Access 2000 report. The recordset source is from a SQL database.

Any help would be appreciated

robp

 

[JerryDennison]

Open a blank report. Put the SQL statement in the Report's RecordSource property. Once there you will then have available all the fields this returns in your field list. You may then place them where and how you'd like on the report. All of this assumes that the correct ODBC drivers have been installed and the DNS setup and the SQL tables have been linked. Of course, once these steps have been taken it's usually easier to setup a query that returns the records you need and then base your report on it.

 

[spwiz]

Thanks for your prompt reply.

If only it was linked tables. This whole access front end as been wrote using VBA and ADO so no linked tables. "I wish"..

Any examples of code to get this recordset into a report would be most welcome...


robp

 

[JerryDennison]

Use VBA to create a Query. You can use the QueryDef method. You should also be able to copy and paste the ADO and SQL from your existing VBA to get the recordset for building the QueryDef. You can then use this query as the basis for you report. There may be other methods, but this one seems easiest to me.

 

[Aivars]

Hi!

There is other way:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

strSQL = "Select * From MyTable Where MyField1 = " & MyCriteria & ";"
Me.RecordSource = strSQL
End Sub

Aivars