My problem is that I can run a querydef returning, for example 3 unique records (as expected), run the querydef again, and no records at all are returned (this is without the background data changing at all), run a third time and the expected results will be shown again. Very inconsistent
Investigation into this problem has revealed that at the point the querydef result set is obtained the recordset has zero records. Whereas opening a normal recordset using effectively the same SQL will show the correct number of records.
It does appear to be a problem using Querydefs on linked ODBC tables, mine are through to an Informix database, whereas another users were through to SQL Server 2000 (http://dbforums.com/t515156.html)
I can confirm that this feature doesn't exist when using Access '97, as that's what we've been using for a few years now, I'm investigating the issues surrounding an upgrade to Access 2000.
If anyone can shed any light on this, I'd be very grateful.