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
However, I want to be able to send the instruction through a middle tier process, something like this:
Set conn = New ADODB.Connection
set rst.ActiveConnection = conn
rst.Source = strSQL
set rst.activeconnection = currentdb.connection
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.