By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Hi Performance across slow LAN using FE/BE - more info & questions

P: n/a
I have recently been trying to determine the best technique to pull the
least amount of info across the LAN link in a slow speed LAN situation
(e.g. < 10 Mbps), where data volume = performance.

After much confusion from reading a lot of info, some of which had
conflicting info, it appears that setting the record source of a form
equal to a sql statement that has the selection criteria as part of the
sql statement, rather than using a stored query and form filter
specification, being careful to use selection criteria that are indexes
in the table - results in pulling across the link only the necessary
index pages to select the appropriate records; subsequently the selected
records are pulled across the link. This looked like the fastest
approach (or so I thought).

From all I have read, using filters in Forms and reports all happens
after the record source has been made available. So when using filters,
ALL the records in recordsource query or table definition are moved from
BE to FE

Potentially better approach.

Obviously with an SQL server backend, I could use a stored procedure and
only return the selected records - avoiding the transfer of the indexes.
But I don't want to switch the backend.

But ... I am using Access 2003 with Jet 4.0. And Jet 4.0 supports
Stored Queries

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

So it seems that if I use the ADO syntax correctly, I can define a
stored query, with a parameter, for which I set the parameter value in
VBA (probably a global variable ??) and then use the query name in the
record source. I think that this will give me a stored procedure on the
BE jet4.0 that only passes the selected records.

MVPs (and others knowledgeable in this), does this sound correct to you?

Anyone done any performance comparisons to see if the ADO "dance" is
worth the additional effort vs. the alternative of defining a sql
statement in the record source with the criteria specified in the sql
statement?

Bob
Mar 2 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
rkc
Bob Alston wrote:
I have recently been trying to determine the best technique to pull the
least amount of info across the LAN link in a slow speed LAN situation
(e.g. < 10 Mbps), where data volume = performance.

After much confusion from reading a lot of info, some of which had
conflicting info, it appears that setting the record source of a form
equal to a sql statement that has the selection criteria as part of the
sql statement, rather than using a stored query and form filter
specification, being careful to use selection criteria that are indexes
in the table - results in pulling across the link only the necessary
index pages to select the appropriate records; subsequently the selected
records are pulled across the link. This looked like the fastest
approach (or so I thought).

From all I have read, using filters in Forms and reports all happens
after the record source has been made available. So when using filters,
ALL the records in recordsource query or table definition are moved from
BE to FE

Potentially better approach.

Obviously with an SQL server backend, I could use a stored procedure and
only return the selected records - avoiding the transfer of the indexes.
But I don't want to switch the backend.

But ... I am using Access 2003 with Jet 4.0. And Jet 4.0 supports
Stored Queries

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

So it seems that if I use the ADO syntax correctly, I can define a
stored query, with a parameter, for which I set the parameter value in
VBA (probably a global variable ??) and then use the query name in the
record source. I think that this will give me a stored procedure on the
BE jet4.0 that only passes the selected records.

MVPs (and others knowledgeable in this), does this sound correct to you?
No initials and never been accused of being knowledgable in public,
but it still sounds incorrect to me.

All that article does is explain the round about way you
have to go to create a saved query using ADOX instead of
DAO and a querydef. Jet is still a file server based database
engine no matter what data access api you use with it.
Mar 2 '07 #2

P: n/a
rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45**********************@roadrunner.com:
... never been accused of being knowledgable in public
J'ACCUSE ...!
Mar 2 '07 #3

P: n/a
rkc
lyle fairfield wrote:
rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45**********************@roadrunner.com:

>>... never been accused of being knowledgable in public


J'ACCUSE ...!
Gesundheit!
Mar 2 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.