Thanks again Wayne,
I have looked at Tony's great site, and I have implemented a persistent link
to the back end.
On the point about queries pulling all the data across the wire, though, I'm
not sure I agree. I've been talking with lots of people about this and there
seems to be a difference of opinion among experienced Access developers.
What I've gleaned so far is that if a query is limited with a "WHERE" on an
indexed field, then Jet can search the index without getting all the
records, and can then figure out which full records to get from the back
end, (we're talking an mdb back end here). For this reason, several sources
I've read suggest limiting the size of a form's recordsource for
performance, even will a file server situation. See the August 05 thread
"Optimizing forms by limiting recordsource- what's really true" in
Microsoft.public.access.multiuser for a discussion of this.
In that discussion, Abert Kallal suggested just using a where condition in
docmd.openform to get the same results, but it seems to me that this _will_
pull all the records across, and then appy the filter after the fact, since
if I turn the filter off, there they all are. (Or did it get all the records
_after_ I turned off the filter?)
-John
"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:lzxWe.1036$7x4.23@newssvr13.news.prodigy.com. ..[color=blue]
> Hi John,
>
> I'm glad you found the problem.
>
> Even if you use a query as the form's Record Source, all of the records
> will be pulled across the wire. The query is run in the front-end file.
> The back-end file is just that, a file. It just sits there and the Access
> application on the has to open the file and read the records. To keep all
> of the records from coming across the wire, you need a way to let the
> back-end do the filtering. That would mean an application/program on the
> back-end to retrieve the records and send only the relevant ones. For
> that, you are talking about something like SQL Server.
>
> To improve performance using a front-end, back-end setup, see Tony's
> suggestions at this link. The one about maintaining an open recordset to
> the back-end file makes a huge difference in performance.
>
>
http://www.granite.ab.ca/access/performancefaq.htm
>
http://www.granite.ab.ca/access/splitapp/index.htm
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "John Welch" <john(remove)welch@cal(remove)central.com> wrote in message
> news:dgdluv01rpm@enews2.newsguy.com...[color=green]
>> Thanks Wayne. No, there was no mispelling - I tested that by purposely
>> spelling the field wrong, and I got a parameter prompt. I did find that
>> if I went up to the menu and chose 'apply filter' , then the form
>> filtered correctly, but not until then. It really has me stumped. I
>> couldn't see any code that turned off the filter directly, but maybe
>> there is something subtle. I'll look harder and report back if I find
>> anything.
>> But I think I'm not going to use a where condition after all because I
>> want to limit the data going over the wire, so I'm going to have the form
>> set up it's own recordsource based on what it gets in its openargs.
>>
>> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
>> message news:sFqWe.1253$2J3.1006@newssvr21.news.prodigy.co m...[color=darkred]
>>> Is there misspelling in the field name, but if so, you should get
>>> prompted for a parameter value. Do you have any code in the form that
>>> may be turning the filter off again?
>>>
>>> No, all records will be returned and the form will filter them. If you
>>> set up an ADP using SQL Server as the back-end there is a filter you can
>>> apply that gets sent to the server before the records go over the wire.
>>>
>>> --
>>> Wayne Morgan
>>> MS Access MVP
>>>
>>>
>>> "John Welch" <john(remove)welch@cal(remove)central.com> wrote in message
>>> news:dgd8ur01gr7@enews2.newsguy.com...
>>>>I have a command button with the following code:
>>>> DoCmd.OpenForm "frmSearchAssignments", , , "SearchAssignmentID = 1"
>>>> (SearchAssignmentID is the PK, auto number)
>>>> When it runs, the form opens but shows all records, rather than going
>>>> to the one I want. If I look at the form in design view, it shows
>>>> 'SearchAssignmentID=1' (without quotes) as the filter in the properties
>>>> list.
>>>> frmSearchAssignments is bound to a query qrySearchAssignments.
>>>> the form's properties are:
>>>> allow filters-yes
>>>> allow edits-yes
>>>> allow deletions-yes
>>>> allow additions-no
>>>> data entry-no
>>>> recordset type-dynaset
>>>>
>>>> Any suggestions for why the where condition isn't being applied?
>>>>
>>>> Also - If this did work correctly, would it limit what gets sent over
>>>> the wire with a file server FE/BE application (as would happen if I
>>>> based the form on a query limited on an indexed field), or is it just
>>>> applying a filter after the fact?
>>>>
>>>> thanks
>>>> -John
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]
