Split Database--Use of DMax/DCount functions

[EPD Hater]

I have split a database--a front-end on the user's PC, and the back-end on a network drive. For me, like others, performance has suffered. I have researched issues on various websites on how to alleviate some of this and found that the use of domain/database functions slows everything down.

I'd like to know if this approach is better and more acceptable. For example, I have a form that aggregates data for reference. This form has 32 controls that are DMax/DCount/DLookup functions. I have replaced them with setting DAO recordsets to SELECT SQL queries in VBA, and then setting the control sources to something like below:

 Me.Control1.ControlSource = "=" & RST.Fields(0)

It loads a little bit quicker, but I am just wondering if these are the things that people are doing from the get-go when designing a split database.

 

[WayneRyan]

EH,

There are several factors that are involved in fe/be configurations.

Primararily, are your forms based on tables? If the recordsource for
your forms is a table (as opposed to a query), then the network is
subjected to a LOT of traffic. If you base your forms on selective
queries (combo/listbox), then you won't see the performance hit that
entire tables require.

As far as the DLookUp's performance is concerned:

Rule #1: Queries (or QueryDefs) are WAY faster than the Domain functions
like DlookUp, DMax, DCount, etc. Recordsets are in the same league.

In general, staying away from the above, results in about a 50 fold
decrease in processing time. Of course, if a DLookUp only took a
thousandth of a second, then maybe the savings isn't worth it.

But, basing a form with controls populated by Domain Functions with
32 fields (over a network) can't be fun!

Just some thoughts ..

BTW,

Is this an unbound app?
Wayne

 

[EPD Hater]

I'm not familiar with binding an access application. What could it be bound to?

Anyhow, most of my forms have queries. Those queries are saved as objects and some are brought into the Form_Open event due to dynamic criteria. However, I am doing a SELECT * in those queries. Would being specific with my columns (just the ones I need) increase performance?

You also said that doing DB.OpenRecordset on SQL SELECT statements is just as bad and that creating QueryDefs is more efficient (keep in mind I want to stick with DAO). Can I do CreateQueryDef() on SELECT statements and how would I reference the results of that SELECT (instead of RST.Fields(0) for RecordSets)?

 

[WayneRyan]

EPD,

If you navigate through your forms by using a selection from list-boxes
or combo-boxes then you won't need QueryDefs. If you use multi-select
list-boxes, you'll have to dynamically build a query.

In your queries, it really doesn't impact performance when you use
"Select *". The important thing is to use the query's Criteria to
restrict the number of records. If you base forms on entire tables,
or queries that capture the whole table, you'll have performance
problems, especially over a network.

Bound forms are just forms where the controls are tied directly to a
table(s) data, even if it's through a query.

In general, you'll find that the Domain functions; DMax, DLookup and
DCount are the slowest way of doing things.

Wayne