I am changing my database to a 3-tier design in Access 2010 ADP and SQL 2005
The forms work fine: the middle tier sends a request to SQL and hands the recordset back to the Access, and all is dandy

The reports work fine in a connected databse, using a
Me.RecordSource = strSQL
statement.

However, I want to be able to send the instruction through a middle tier process, something like this:
Set conn = New ADODB.Connection
conn.Open GetConnectionString()
set rst.ActiveConnection = conn
rst.Source = strSQL
rst.open
set rst.activeconnection = currentdb.connection
conn.Close
set me.recordset = rst
my reason for this is to reduce the connection time to a minimum. However, when I use this technique, the recordset has no records in it - the NoData event fires.

I debugged the procedure, and found that the rst object has the records right through, but when me.recordset is set to be rst, the me.recordset never gets any records.

Any ideas?


Stephen