How to set RecordSource to ADO recordset

[David P. Lurie]

Access 2003 mdb FE with linked tables to PostgreSQL 7.4.3 BE

How can the RecordSource property of a report be set to use an SQL
pass-through query, created at runtime as an ADO recordset?

If that is not possible, should the pass-through query be created
programmatically as a query object and set as RecordSource with an OnOpen
procedure, then deleted with an OnClose procedure?

The goal is to get a result similar to a filter set at runtime, but
processed at the server, like a server-side filter in an adp. Client-side
queries with PostgreSQL are too slow to for a filter to be used.

Example recordset:

Sub TestODBC()
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim strqry As String ' SQL pass-through query string generated at runtime
cnn1.Open "Provider=MSDASQL;DSN=PostgreSQL30;"
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenDynamic
rst1.LockType = adLockOptimistic
rst1.CursorLocation = adUseClient
rst1.Open strqry, cnn1
....

Thanks,

David P. Lurie

 

[Douglas J. Steele]

A passthrough query will certainly work. In fact, it's not even necessary to
create it and delete it each time: you can simply reset the SQL of the
query.