Since there seem to be so many talented developers on this site I thought I would present this problem that I am experiencing:

The db that I have developed for our company runs very quickly on the local Lan BUT any form that has a subform in it loads sssssllllllooooowwwwlllyyy on the VPN. Now, it is true that the subform can contain multiple records wheras the main form only contains one "filtered" record but there is not enough data ever in the subform to justify the slow response.

My theory is that since the main form is filtered from a few hundred (possibly thousands) of records the system is having to search 10's of thousands of records to populate the subform with. Of course I could be way off base here but I can't figure out what the problem is otherwise. The tables for the form/subform always have primary indexes on them (generally the linking field) so I don't think it's an index problem.

So before doing anything else I thought I would pose the question:
Would I be better to use the linking field to create a recordset of data to populate the subform as opposed to the way I am doing it (using a filter for the main form)?
I could do this but it will take a lot of work as there are a lot of forms that would have to be redesigned.

One word of advice I would have for anyone designing a db for use over a VPN - start testing the db on the VPN immediately - that way if you are using a methodology that won't work well you will find out immediately!

Thanks for any help - this forum is great!