Do I need to set up a persistant connection?

[Guest]

Hi.

I posted a few days ago, asking advice about a split database running slow.
Allen Browne directed me to this site. I have read this and have a follow up
question.

http://www.granite.ab.ca/access/performancefaq.htm

How can I tell if I need to set up a separate table/form arrangement (or
whatever) to ensure a connection between FE and BE is maintained? I.e. does
my design mean that I already have one built in? I have a Tab form as my main
switchboard. Each tab clicked brings up a form/subform. The Front End (FE) is
on my PC (I'm using a copy of my mdb as a testbed), the Back End (BE) is on
the server. FE is currently mdb, not mde. Both FE and BE open an ldb file
when I open the database. I am currently the only user accessing this split
mdb and it is so slooooow. A particular report, containing a few DLookups,
takes 3 secs to load on the unsplit version and over 6 minutes on the split
version (yes, I timed it). Don't even mention Mail Merges!

Any ideas whether I need to set up a persistant connection? Also, would
making the FE an mde speed things up?

Thanks, JohnB

 

[Allen Browne]

John, I suggest that you only do this (create a persistant connection) if it
measurably solves a problem you have. For many databases, you can get by
without it, and it is better not leaving things open that don't need to be
open.

An MDE will not speed things up compared to a compiled MDB. However, it
might help avoid some issues, such as users fooling with the interface,
decompiling it, or some corruption issues related to opening an MDB in
multiple versions of Access (where it does decompile, and the binary is
different between different versions.)

 

[Guest]

I'm fairly sure I do have a persistant connection. Thanks for confirming that
creating an mde will not speed things up. I think the users are just going to
have to put up with a slower database, in order for it to be more corruption
resistant.

Cheers, JohnB

 

[Pat Hartman\(MVP\)]

DLookup()s are extremely inefficient. We talk a lot about normalization and
how data needs to be stored correctly but we don't talk enough about how to
turn all that data back into information. I would hazard a guess that in
the vast majority of cases, DLookup()s can be eliminated entirely and
replaced by the technique of basing forms/reports on queries that join the
main table to the lookup table (with a left join if the relationship is
optional). This join, if there are more than a trivial number of rows in
the tables, will be far superior to the DLookup().

Other speed reductions can be achieved by replacing other domain functions
with aggregate (totals) queries.

 

[Guest]

Thanks Pat.

I'll have a look at redoing things to avoid too many DLookups.

Cheers, JohnB