I am converting an Access 2002 .mdb frontend to .adp. The backend .mdb tables have been imported into SQL, linked to the .adp via a system DSN.
The .mdb contained many forms, based on Queries. In these forms I had command buttons which used Applyfilter to display subsets of the query resultant dataset. Some of these Forms are displayed as continuous one-line forms to show a list of records. In the .mdb this was a very quick way to display the subset of records (presumably because the queries were in the local front-end?)
Now along comes .adp so
a) I have to find an alternative to all the Queries. I have tried creating a View using the same criteria as the original query, but for some reason the screen update is now VERY slow. I suspect the conditional formatting I've used to flag certain fields in colour, etc, may be partly to blame, as well as the View now being in the back-end.
b) I am also having trouble using DoCmd.Applyfilter in code - it keep saying it needs either the Filter Name or a valid SQL statement as parameters - I've tried with either, and with both - it still won't have it. It works if I use Applyfilter in a macro (apart from being slow as described above) - but I'm trying to get rid of macros so I'd rather not do that.
c) Tried setting the Form's filter and serverfilter properties - it works if I enter the argument in the Properties dialogue, but not in the code of the command button's OnClick event procedure.
Is there better (faster!) approach than filtering a View dataset? Would a Stored Procedure be a better fit?
Could it be that other code and maybe some forgotten DAO inherited from the access .mdb would be causing the sluggishness? (I wonder particularly about Date and Time functions such as Now() which I probably need to replace with GetDate and GetTime, although I've already remmed all that out to no avail.)
Any advice / shared experience would be much appreciated.
Start Free Trial
