Duplicating split forms

[speakers_86]

I cant duplicate the network now, since I'm on the road. Also, if I do it, there won't be any fiber, just an ethernet cable.

 

[Galaxiom]

I am using DLast about five times.

Each Domain function is running a separate query. Can you change them into a single query?

BTW Do you know what DLast actually returns? If you are expecting the most recent record that was added to the table you could get a surprise one day.

 

[vbaInet]

I can't open the db, it was saved in 2013 but I run 2010. When you get to your dev environment upload a 2010 version.

 

[vbaInet]

Don't worry, it's opening alright now.

 

[vbaInet]

I'm going offline now, pretty tired. Can you upload a FE that only includes the tables that are in the BE and tell me which queries are slow.

DLast() is of course slow and it can be properly re-written, but there could be more to it than just DLast() and that's what I want to check out.

 

[speakers_86]

If I am not mistaken, Dlast returns the last record added to the table, despite any sorting or grouping when used on a query.

Everything in question is in what I posted. Both form and subform use qryNotes2. That query has a subquery called qryNotes.

 

[Galaxiom]

If I am not mistaken, Dlast returns the last record added to the table, despite any sorting or grouping when used on a query.

DFirst() and DLast() can both be unpredictable.

If you never delete records they might return what you expect. However a compact and repair especially after deleting records can have the records completely scattered through the table.

Better use DMax() or DMin() on a field that reliably determines the "First" or "Last" record.

 

[speakers_86]

Thats good to know, thanks.

 

[vbaInet]

There are some links on the FE that aren't in the BE and the Link Manager fails when it encounters one of those which means the entire re-linking fails. I don't have time to start matching which tables are in which.

 

[speakers_86]

Ah, you just want all the tables.

 

[vbaInet]

That's right.

speakers. I can't see anything obvious in qryNotes and qryNotes2. Where are you using the DLast() and DFirst() functions? Galaxiom has advised on what you should use and you could use a subquery instead.

In the meantime, test it with just one record in each of the tables.

 

[speakers_86]

Im not using it in those queries, it is in the code at various points in the whole project. I am already moving away from using them. Are all of the domain functions not good to use?

 

[vbaInet]

They are generally slower. If it's needed in code, then you could consider using a Recordset whose datasource utilises Min(), Max() functions.

 

[speakers_86]

Okay, thanks for the help. If I figure out the speed issue I will let you know.

 

[vbaInet]

Did you try opening the form with just one record in the table?

 

[speakers_86]

I will once I duplicate the network. I don't experience the problem when the backend is on the same HDD.

 

[Galaxiom]

[Domain functions] are generally slower.

Not really per se but they are slow when misused. In effect, each pplication of domain function is running a query.

For tasks such as a ControlSource for a singe textbox on a single form or even looking up a value to use in a where clause comparison they they are fine. Like any other query they work much faster if the field is indexed.

Problems start when a developer puts several text boxes, each with a separate Domain function as its ControlSource, onto a form or uses them as as a ControlSource in a ContinuousForm. This makes the form slow to load.

In the case of the separate textboxes it can usually be overcome by using a subform with a RecordSource that returns all the data for the textboxes in a single query.

The Continuous forms situation is usually overcome by joining into a query. Unfortunately though, this often makes the RecordSource query non-updateable. If that matters then the Domain function can be a good way around the problem and we just live with the lack of speed.

Worse still is using a domain function inside a query where every record must be processed with the function. If the query results must be ordered it can slow a query to a crawl and any form using the query as a RecordSource becomes painfully slow to load.

 

[vbaInet]

Understood! And some good advice too. But what I'm comparing domain aggregate functions to is other methods of retrieving the same value e.g. via a Recordset. I've shown several methods in past threads which return values much much quicker than domain aggregate functions and I'm sure that you're aware of this method.

 

[Galaxiom]

But what I'm comparing domain aggregate functions to is other methods of retrieving the same value e.g. via a Recordset. I've shown several methods in past threads which return values much much quicker than domain aggregate functions.

That is good to know. I have never conducted side by side tests.

I do recall someone checking out a DCount many years ago and I remember that having an index on the field made such a vast difference that the times to return the answer fell to trivial values.

I kind of assumed the code behind domain functions basically opened a recordset too, so expected similar performance.

 

[vbaInet]

I don't know the engineering behind domain aggregate functions but like you I would expect that they open some sort of recordset too.

For example, something like this would run much quicker than the DMax() equivalent:

Currentdb.OpenRecordset("SELECT Max(Field) FROM Table WHERE SomeCriteria;", dbOpenForwardOnly).Fields(0)