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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question