Are MDEs faster and/or more reliable?

[Leslie Isaacs]

Hello All

I have a split FE/BE A97 mdb. The BE (278mb) is on a server, with 7 FEs
(115mb) on 7 PCs: the PCs are running W2K professional, with 512mbRAM on
each. This is all starting to creak a bit: we are increasingly frequently
getting database corruptions and "can't open any more tables ..." and other
messages that I think are due to our being close to access's limits in terms
of sharing and effective mdb size.

I have been wondering about converting the FEs to MDEs: would this be a good
idea? I understand that I would have to make any FE design changes to the
original FE MDB and then re-convert to MDE, but apart from that are there
any other likely problems with the users working on MDEs (they don't need to
make any design changes).

Thanks for any advice.
Les

 

[Dale Fye]

..mde files are a very effective way of preventing users from making changes
to code within your application, but I have not found that they will
significantly (if at all) speed up your operations.

What type of connection do your users have to the backend server? Is it a
LAN, WAN, wireless? That would be the first thing I would look at.

You have to understand that Access does all of the processing work on the
front-end. So if you have a table with 100,000 records, and your user has a
report that only involves one of these records, the Access/Jet combination
will send all 100,000 records to the users PC, and will then discard 999,999
of them. Over a network, with multiple users, this can slow the entire
network down.

So, as the size of your backend increases (this has nothing to do with that
2300 calorie burger you had for lunch), you might want to give serious
thought to upsizing your backend database to SQL Server. The Express version
is free and does almost everything that its more costly brother does. The
advantage of a SQL Server backend is that almost all of the processing is
done on the server, so the above query will only pass 1 record back across
the network. You can still use linked tables to link to the Server side
tables, and you will most likely have to make very few changes to the
front-end to make this work.

Good luck
--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Sylvain Lafontaine]

You have to understand that Access does all of the processing work on the

front-end. So if you have a table with 100,000 records, and your user has
a
report that only involves one of these records, the Access/Jet combination
will send all 100,000 records to the users PC, and will then discard
999,999
of them. Over a network, with multiple users, this can slow the entire
network down.

Not necessarily true: Access can use indexes to limit the number of records
transmitted. However, if there is no index avalaible or some VBA function
is used at the wrong place or whatever else reason than yes, all the records
could be transmitted to the client.

Using SQL-Server as a backend will not necessarily increase the overall
speed of the application because there are many factors to take into account
(in fact, it can even be slower) but at least, the corruption will probably
cease. The only real problem with SQL-Server would be the absence of
pessimistic locking for editing but if you don't use this feature of Access
then upsizing to SQL-Server should be relatively painless. In all cases, by
using SQL-Express, it won't cost much to try it.

 

[Rick Brandt]

.mde files are a very effective way of preventing users from making
changes to code within your application, but I have not found that
they will significantly (if at all) speed up your operations.

What type of connection do your users have to the backend server? Is
it a LAN, WAN, wireless? That would be the first thing I would look
at.

You have to understand that Access does all of the processing work on
the front-end. So if you have a table with 100,000 records, and your
user has a report that only involves one of these records, the
Access/Jet combination will send all 100,000 records to the users PC,
and will then discard 999,999 of them. Over a network, with multiple
users, this can slow the entire network down.

As has been stated MANY times in these groups, the above is absolutely NOT
how Access works over a network. It pulls exactly the same thing over the
network that it would pull from your hard drive if the tables were local.
That is, it will pull pages of the appropriate index until it finds the
index entry for the desired record and then it will use that index data to
pull the ONE record (or at most the one page of data containing that record)
from the table.

You can do things that will cause entire tables to be pulled, but it is very
much not what happens the vast majority of the time.

 

[Dale Fye]

Can you provide me with a reference that explains this better.

I've been told on numerous occassions that this is the case, and until now
have never had anyone tell me otherwise.

 

[Tony Toews [MVP]]

Can you provide me with a reference that explains this better.

Basic theory on how indexing works. Be definition reading an index
fetches only the records you are interested in. In Access/Jet though
it's really fetching the page the records reside on as there would
usually be more than one record on a 2k/4k page.

I've been told on numerous occassions that this is the case, and until now
have never had anyone tell me otherwise.

You certainly haven't repeated that here or we would've jumped all
over you previously. <smile>

Seriously this is one of the myths about how Access/Jet works that we
stomp on.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I have a split FE/BE A97 mdb. The BE (278mb) is on a server, with 7 FEs
(115mb) on 7 PCs: the PCs are running W2K professional, with 512mbRAM on
each. This is all starting to creak a bit: we are increasingly frequently
getting database corruptions and "can't open any more tables ..." and other
messages that I think are due to our being close to access's limits in terms
of sharing and effective mdb size.

I have been wondering about converting the FEs to MDEs: would this be a good
idea? I understand that I would have to make any FE design changes to the
original FE MDB and then re-convert to MDE, but apart from that are there
any other likely problems with the users working on MDEs (they don't need to
make any design changes).

NO, MDEs won't be faster or more reliable as far as corruptions go.

For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

Can you provide me with a reference that explains this better.

I've been told on numerous occassions that this is the case, and until now
have never had anyone tell me otherwise.

I've been told on numerous occasions that the moon is made of green cheese.
But I take into account the expertise of the teller before accepting that.
If no one has, up to now, corrected your misunderstanding, that likely
indicates you don't spend much time in this newsgroup, or any other where
the question has arisen, or haven't read posts and responses on the issue.

If you have a reference to any Microsoft publication that states that Access
brings the entire table or entire database across the LAN, I am sure they
would appreciate your letting us know, so we can report it, and they can
correct it.

If it's someone who was trying to sell a server DB solution, or an IT
department trying to discourage the use of Access, you have a "vested
interest" reason why they would misrepresent the situation.

Dan Haught, one of the principals of FMS, Inc., a long-time provider of
Access support utilities and consulting, wrote a book on the operation of
the Jet database engine which, I suspect, goes into details on this subject.
It is long-time out-of-print and, unfortunately, when I tried to obtain a
used copy, I could not get one.

Larry Linson
Microsoft Office Access MVP

 

[Dale Fye]

Thanks, guys, for setting me straight on this.

As I under stand your comments, it is the indexing that provides for a less
than full pull across the network, so as long as your query fields are
properly indexed, you should get a relatively small result set. What if one
of the fields in your query is not indexed, will it then pull back one or
more "pages"?

Dale

 

[John W. Vinson]

As I under stand your comments, it is the indexing that provides for a less
than full pull across the network, so as long as your query fields are
properly indexed, you should get a relatively small result set. What if one
of the fields in your query is not indexed, will it then pull back one or
more "pages"?

It depends on the query plan. The JET optimizer will look at the statistics of
the data (which, I believe, get reset whenever you compact) and decide which
indexes to use, and when to do a full table scan instead. If there are some
fields used for criteria with indexes and some without, the recordset will
(usually) be filtered by the index and the remaining records scanned using the
other fields.

It's possible to defeat the optimizer and force a table scan, e.g. by using a
calculated field or a function call as the field being searched, or using LIKE
"*" & [search term] & "*" to search for text within (but not at the start of)
a field, etc. And the optimizer isn't perfect and can sometimes come up with a
non-optimum plan.

 

[Dale Fye]

John,

When I think back to the discussion I had with my source (I'll leave names
out because I don't want to misquote someone), one of the questions I asked
had to do with using mixed (local and backend) tables, and it was in
relation to using Access or SQL Server as the backend.

In many of my databases, I have several local tables that allow users to
select subsets of a particular table. For example, I'll have a Users table
in the backend and a local_users table in the front end, where the local
users table has a UserID, UserNames (concatenated into a single field) and
also includes an IsSelected (Yes/No) field. I can then present these names
to my application user in a continuous form to allow them to check the users
that they want to send a message to (as an example).

If I then join this local_users table to another table (linked from the
backend) on the UserID field, which is indexed in both tables, what effect
will that have on the query plan?

Dale

As I under stand your comments, it is the indexing that provides for a
less
than full pull across the network, so as long as your query fields are
properly indexed, you should get a relatively small result set. What if
one
of the fields in your query is not indexed, will it then pull back one or
more "pages"?

It depends on the query plan. The JET optimizer will look at the
statistics of
the data (which, I believe, get reset whenever you compact) and decide
which
indexes to use, and when to do a full table scan instead. If there are
some
fields used for criteria with indexes and some without, the recordset will
(usually) be filtered by the index and the remaining records scanned using
the
other fields.

It's possible to defeat the optimizer and force a table scan, e.g. by
using a
calculated field or a function call as the field being searched, or using
LIKE
"*" & [search term] & "*" to search for text within (but not at the start
of)
a field, etc. And the optimizer isn't perfect and can sometimes come up
with a
non-optimum plan.

 

[Tony Toews [MVP]]

Dan Haught, one of the principals of FMS, Inc., a long-time provider of
Access support utilities and consulting, wrote a book on the operation of
the Jet database engine which, I suspect, goes into details on this subject.
It is long-time out-of-print and, unfortunately, when I tried to obtain a
used copy, I could not get one.

I just looked at mine and while it does talk about optimizing indexes
and such nowhere does it explicitly state something like "Indexes
reduce the number of Jet pages read." It just states things like
"makes the query run faster."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I have a split FE/BE A97 mdb. The BE (278mb) is on a server, with
7 FEs (115mb) on 7 PCs: the PCs are running W2K professional, with
512mbRAM on each. This is all starting to creak a bit: we are
increasingly frequently getting database corruptions and "can't
open any more tables ..." and other messages that I think are due
to our being close to access's limits in terms of sharing and
effective mdb size.

I doubt it. Your environment sounds perfectly good to me for the
software you're running. I would suggest that you've likely got
unpatched installations of Access, without the proper services packs
for Access and Jet. Or you have hardware problems or software issues
on the file server.

I have been wondering about converting the FEs to MDEs: would this
be a good idea?

It would likely accomplish nothing whatsoever in making your
environment more stable, since it wouldn't change any of the factors
that are the likely cause of your problems.

I understand that I would have to make any FE design changes to
the original FE MDB and then re-convert to MDE, but apart from
that are there any other likely problems with the users working on
MDEs (they don't need to make any design changes).

The only advantage I can see is that if your unstable environment
leads to a runtime error, in an MDE it won't reset your global
variables, so it's slightly more recoverable.

But you need to address the underlying problems regardless of
whether you distribute a front end MDB or MDE.

 

[David W. Fenton]

Can you provide me with a reference that explains this better.

The Jet Database Engine Programmer's Guide explains it in detail,
but that's not online.

I've been told on numerous occassions that this is the case, and
until now have never had anyone tell me otherwise.

It's been posted here in the Access newsgroups bazillions of times.
I take you don't read here very often?

 

[John W. Vinson]

If I then join this local_users table to another table (linked from the
backend) on the UserID field, which is indexed in both tables, what effect
will that have on the query plan?

I simply don't know, and don't even have a good idea of how to find out! About
the only suggestion would be to set up two databases, one with the tables all
in the same database and the other with them split across databases.

My *guess* is that the optimizer may find it much harder to deal with a query
across different databases but I have no evidence on the subject.

 

[Dale Fye]

Dave,

I'm on these groups every day, although I must admit I spend most of my time
in the FormsCoding and Queries groups.
--

Dale

email address is invalid
Please reply to newsgroup only.

 

[Clif McIrvin]

Dave,

I'm on these groups every day, although I must admit I spend most of
my time
in the FormsCoding and Queries groups.

I'd guess Dale is like me ... we're here, but may only scan the
half-dozen or so messages per day whose subject lines catch our eye ...
so those bazillions of mentions are out there in the Google archives if
a quest takes us there, but may never catch our eye (until, like now, we
bump into the issue!)

As always, THANKS for all the help and advice y'all donate!!

 

[Tony Toews [MVP]]

I'd guess Dale is like me ... we're here, but may only scan the
half-dozen or so messages per day whose subject lines catch our eye ...
so those bazillions of mentions are out there in the Google archives if
a quest takes us there, but may never catch our eye (until, like now, we
bump into the issue!)

Exactly. I'm the same way. If I see the word dsum or dlookup or
newbie in the subject I ignore that thread.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

You could turn the query plan analyser on and try it, but plain
evidence is given by the behaviour of Declarative Referential
Integrity across two databases: Access can't create foreign
key indexes across two databases.

(david)

 

[John W. Vinson]

You could turn the query plan analyser on and try it, but plain
evidence is given by the behaviour of Declarative Referential
Integrity across two databases: Access can't create foreign
key indexes across two databases.

OTOH it can use indexes for query performance (if not for RI enforcement).
I'll try setting some such up and see what the query plan says - I'm curious
now!