Split database and performance

[dhstein]

I came across Albert Kallal's write up on splitting a database and he
mentioned that there could be a performance issue. I seem to be experiencing
this - although it isn't terrible performance - just could be a little
better. Albert mentions opening a table in the main form and keeping it
open. His document was written before Access 2007 (I believe) so I'm
wondering if this is still a solution for Access 2007 or do I need to look
elsewhere to solve the performance problem? Thanks for any
advice/suggestions on this issue.

David

 

[Douglas J. Steele]

As far as I know, it should still work with Access 2007.

 

[Tony Toews [MVP]]

I came across Albert Kallal's write up on splitting a database and he
mentioned that there could be a performance issue. I seem to be experiencing
this - although it isn't terrible performance - just could be a little
better. Albert mentions opening a table in the main form and keeping it
open. His document was written before Access 2007 (I believe) so I'm
wondering if this is still a solution for Access 2007 or do I need to look
elsewhere to solve the performance problem?

Yes, still an issue for A2007.

Access Performance FAQ page
http://www.granite.ab.ca/access/performancefaq.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/

 

[Tired]

I asked this question earlier today. It would be nice to know the answer. The
answer I was given was to "Work through Tony Toews' suggestions here:
http://www.granite.ab.ca/access/performancefaq.htm

None of these worked for me. Let me know if you have any luck. Maybe I need
to look for answers on a non-microsoft website...

 

[Paul Shapiro]

If the backend is now on a different computer, you could check that
anti-malware scanning is disabled for .mdb files and/or the folder
containing the mdb, both on the server and the client. Sometimes newer
network drivers make a big difference. I found much better performance when
I keep the frontend on my local machine, not on the server with the rest of
my documents and files.

 

[Tired]

I think remote desktop works the best in our case. There are few problems
with performance, i can set the directory properties to read-only or password
protect the database. It works better than any of the performance enhancing
suggestions I've seen so far. The split database is a great concept but
apparently they haven't worked out the bugs.

 

[dhstein]

I hadn't seen your earlier post until you mentioned it here. I don't have
the same level of performance problems that you're seeing. The only thing I
can add is the following. We were seeing a very slow response in opening
excel files and we found that there were drive mappings in use that were
broken/dead/missing etc. When we disconnected those drives the excel problem
was resolved. So a wild stab here is to make sure that you don't have that
situation. We only have 4 - 5 users at the moment and the response isn't too
bad - just a little slow - so I'm not sure what else to suggest.

 

[Albert D. Kallal]

I asked this question earlier today. It would be nice to know the answer.
The
answer I was given was to "Work through Tony Toews' suggestions here:
http://www.granite.ab.ca/access/performancefaq.htm

None of these worked for me. Let me know if you have any luck.

As mentioned, you seem to hint that the persistent connection trick did not
help....it usually does.

So, a few more things:

distribute a mde, or now a accDE to each computer.

Have the startup form open up a conneciton to any table..and KEEP that
form/connection open at a all times....

disable the track name auto correct (From Tony's list).

Have you tried a accDE on each machine???

 

[Douglas J. Steele]

How slow is your network?

 

[David W. Fenton]

Yes, still an issue for A2007.

Access Performance FAQ page
http://www.granite.ab.ca/access/performancefaq.htm

I've only used A2007 with MDBs, but does the ACE use LDB files with
ACCDBs? And is the problem the same? And solved in the same way?

If so, that's a pretty strong indication that my statement that
ACE=Jet 4.5 is true, despite all the objections of those who seem
vested in the idea that ACE is some brand-new database engine
unrelated to Jet (which is patently untrue, of course; the question
is only how different ACE is from Jet 4).

 

[David W. Fenton]

The split database is a great concept but
apparently they haven't worked out the bugs.

Er, what? Splitting has been the way to go for multi-user apps the
whole time I've been using Access, i.e., dating back to 1996 (Access
2). There are no bugs. There *will* be performance differences
comparing certain scenarios in certain network environments with
certain hardware and software environments (AV software is a
notorious problem, and you should make sure your AV software does
not scan MD? and ACCD? files).

I've been deploying split apps exclusively for 13 years now and have
never encountered problems.

A2007 is reported to provide noticeably poorer performance than its
predecessors, and that, ultimately, may be the real problem. It's
not splitting per se, but A2007's performance problems that are to
blame.

 

[Tony Toews [MVP]]

I've only used A2007 with MDBs, but does the ACE use LDB files with
ACCDBs? And is the problem the same? And solved in the same way?

Assumptions on my part.

If so, that's a pretty strong indication that my statement that
ACE=Jet 4.5 is true, despite all the objections of those who seem
vested in the idea that ACE is some brand-new database engine
unrelated to Jet (which is patently untrue, of course; the question
is only how different ACE is from Jet 4).

Umm, who has stated that ACE is brand new? AK? Well, consider the
source then.

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 Kahm]

<<Umm, who has stated that ACE is brand new? AK? Well, consider the
source then.>>

Try to be careful when using initials, please. AFAIK, there's more than one
AK on the forum...

Larry

 

[Tony Toews [MVP]]

<<Umm, who has stated that ACE is brand new? AK? Well, consider the
source then.>>

Try to be careful when using initials, please. AFAIK, there's more than one
AK on the forum...

You're correct. My apologies to all the AKs except for Aaron Kempf.

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/

 

[James A. Fortune]

I've only used A2007 with MDBs, but does the ACE use LDB files with
ACCDBs? And is the problem the same? And solved in the same way?

If so, that's a pretty strong indication that my statement that
ACE=Jet 4.5 is true, despite all the objections of those who seem
vested in the idea that ACE is some brand-new database engine
unrelated to Jet (which is patently untrue, of course; the question
is only how different ACE is from Jet 4).

Although your guess might be true, I'm going to propose something
shocking and speculate further that maybe JET doesn't really exist!
That wild intuitive leap is a true shot-in-the-dark. But it is not
totally unsubstantiated. When a former customer asked me to recover
some Excel files he deleted by accident on his laptop hard drive, my USB
laptop hard drive connector allowed me to poke around in the recovery
partition on the hard drive. The recovery partition contained all the
installation software for restoring the laptop's software to the point
it was at on the purchase date. Some of the startling similarities
between the JET installation file structure and SQL Server setup files
led to the idea that maybe JET is really a toned down derivative of SQL
Server. Maybe JET Red and JET Blue are just different option
histories/patches applied to some version of SQL Server. Although my
guess might be totally off, consider:

http://en.wikipedia.org/wiki/Microsoft_Access_Development

http://en.wikipedia.org/wiki/Adaptive_Server_Enterprise

Microsoft had a nascent version of SQL Server shortly before Access 1.0
was created. It would make sense at that time to use at least the newly
acquired technology of SQL Server in order to come up with a simple
database engine that could challenge Borland's Paradox, and to a lesser
extent FoxPro. At least some dependence on SQL Server is already quite
likely. So perhaps SQL Server was patched instead of starting JET
totally from scratch. If a static version of SQL Server is being
patched, then I would say that JET/ACE really does exist. If patches
get modified to work with the latest version of SQL Server, then I would
say that ACE/JET does not really exist. The former seems more likely.
I think Microsoft wants us to believe that they are going through a
complete development cycle for each "brand-new" database engine.

James A. Fortune
(e-mail address removed)

 

[David W. Fenton]

Assumptions on my part.

Shouldn't you test?

Umm, who has stated that ACE is brand new? AK? Well, consider
the source then.

No, not Aaron. There are a lot of people in a lot of forums who seem
heavily invested in the idea that ACE is something completely
different from Jet, and thus Jet is dead. These are some of the same
people who make comments about A2007 no longer supporting
replication and so forth. I see the same attitude at
StackOverflow.com from a couple of people who tend to dog my
comments about Access<>Jet whenever I assert that ACE=Jet.

I'm perfectly well aware of the truth, but some people (who seem to
be hopeless wed to whatever MS tells them) seem to want to resist
it.