Access MDB should have be used

you need to move to SQL Server

Access MDB has been obsolete for a decade

While the following list is for a2000, there is some suggestions for a97.

a few things:

First, upgrading to sql sever often does not speed these applications up.
(in fact, sometimes they slow down). Simple knee jerk solutions like
replacing the server, or adding sql server are not going to fix these
problems (and, as you seen, your actually experiencing a slow down right
now).

You don't mention if the application is split or not (I hoping it is
split -- however, lets leave this issue out for the time being, but it is a
important one, and unfortunately you failed to mention this detail (the fact
that you NOT mentioned this detail speaks volumes about the skill level and
support your exercising right now - not good!!).

First, with the real server, issues such as file permissions, security, and
a host of other issues now come into play

However, here a few things:

keep the back end "low" in the file hierarchy (if you go deep in terms of
folders, things can slow down). So, keep the back end mdb LOW in the folder
system. (one deep at most).

Since access 97 is very old, then stick to 8.3 file naming conventions.
(and, keep folders 8 or less characters in length, and don't use spaces in
the file names. (there is some "gear" switching that the network has to do
when you don't use legacy file naming here).

The next thing is to try a persistent connection. (this will requite
developer knowledge of ms-access). To "test" this connection idea, you can
simply open up a table in the application (on a workstation), and then
minimize this table. now, while in the appcation, launch that form ..it
should run fast. The "persistent" connection concept is simply something
that forces (keeps) open the file at all times, as there is a RATHER LARGE
overhead WHEN a file is being opened and closed repeat and triggers all that
extra processes and security stuff that the new box has.

And, while were at this, ensure that virus protection software is not
interfering (on both client and server sides). I would consider disable all
virus stuff during testing...

Ah, SBS?  SMB signing.

See the Disable Server Message Block (SMB) Signing section at
http://www.granite.ab.ca/access/performancefaq.htm

Tony

What you mean is that it uses a Jet 3.5 back end.

Access is not involved at all, except insofar as perhaps the
original MDB was created in Access.

Go here:

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

and look for the things that are server-based and network issues.

Also, you might want to check if there's AV software involved.

The biggest issue is where you try to load too many records at once
and this is regardless of whether it is an access backend or sql
server.

Just filtering the data prevents most issues, but normally, speed
issues like you raise are due to too many joins.

e.g. I had a client once who had 20 tables linked to a member table
and wanted every member with tabs showing every linked table.

It took 2 minutes to load.
Then by only loading the members and showing one linked table at a
time, it loaded in 2 seconds.

The design is sooo important....

Regards,
Tom Bizannes
Microsoft Access Development
Sydney,Australia

To report back on what was done to eventually resolve this issue.

The first thing I'd done before coming to these boards was disable
'Opportunistic Locking' on both server and clients. In the past, and in a
peer2peer situation, this typically worked. Microsoft Support has an article
at http://support.microsoft.com/?kbid=296264

However the problem persisted. I was then steered towards
http://www.granite.ab.ca/access/performancefaq.htm, and systematically went
through the discussions there. Most of them have provided an inkling for
future work, but I was under urgency to resolve the current crisis.

One link,
http://groups.google.com/group/microsoft.public.windows.server.sbs/browse_frm/th
read/7188759b0c85bf5a/e9f11abbad2c36b8,
with a reference to 'enablesecuritysignature' proved to be extremely
effective. As did setting 'SharingViolationDelay' as per
http://support.microsoft.com/?id=150384

The query that started this thread, now takes 2-2.5secs to run, whereas
previously it was running to 3minutes.

Even after all of these steps there was still some erratic behaviour when it
came to performance on the clients specific Access app. Checking all the
network cards speed settings revealed some were set to 'Force 100 Full
Duplex', changing these to 'Auto' provided an amazing improvement.

Now the system is stable and performing well, I can focus on development of
the replacement solution. It will have a SQL backend and initially an Access
2003 frontend. The links and input in these threads are, and will, prove
invaluable.

Thanks for the help and the positive input.

Paul_in_NZ