Access 2007 Simultaneous User Speed Issue

[Guest]

The department I work for has a records management database that I wrote and
administer. It runs 24-7 in another location. Whenever I try to open it in
the new Beta, Access crawls, and at times has either frozen beyond all reason
or just shuts itself down. I'm trying to figure out what I'm doing wrong or
what I can do to fix this issue. The database is in Access 2000 format, the
other computer runs Access 2003 on Windows 2000, the database is stored
locally on that computer, and I'm accessing the database over the corporate
network with an ethernet LAN connection using Access 2007 Beta 2 on Windows
XP Home. I've subsequently opened the database on my computer using Access
2003 without any problems and as fast as usual. I've also opened similar
databases over the same network connection without adverse effects on speed.
Upgrading the other computer's version of Access and upgrading the database
version are not really options. Any help would be appreciated

 

[Albert D.Kallal]

. I've also opened similar

databases over the same network connection without adverse effects on
speed.

Well, I suppose the word "similar" is a matter debate. Regardless, you not
comparing the same setup, so, there is not a lot we can learn here until
some tightening up of the comparisons can be done.

Further, when you are going to run ms-access with multiple users, and
*different* versions of ms-access, you need to split. This is due to the
binary code, and compiler that tries to run the code. Each user will be
using a *different* version of code.

Further, you can't really allow more then one user into the front end, and
expect *any* type of reliably. I explain why here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Upgrading the other computer's version of Access and upgrading the
database
version are not really options.

Gee, you are running what is called a beta! I can remember trying to run
windows xp beta on my computer. It just crawled!!!..and no optimization had
be done yet. When the production version came out, I was able to easily run
windows xp on a old 600mhz computer, and it ran just fine.

Having said the above, you not tested your appcation on your LAN with any
other version of ms-access, so, we really don't know anything here yet.

Also, what kind of LAN? Further, have you tried using a persistent
connection? We see about on average 1 or more post here a day about slow
performance of a ms-access application when more then ONE user is in the
application. And, 9 out of 10 times, this performance problem is fixed by
using a persistent connection. So, "over and over" like a broken record, I
shall suggest to try a persistent connection, and see if that fixes this
problem. Try that. This performance tip, and more things to check in terms
of "known" "issues" found here:

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

Simply work your way throught the above list. One of the above should fix
your problem. If none due, it certanly might be a new issue with the beta...

 

[aaron.kempf]

I would reccomend not using Access 2007 and not using MDB files.

use Access Data Projects.

it works over a network for starters; it is a LOT more robust going
over WANS specifically.

 

[aaron.kempf]

and for the record; don't use betas and expect decent performance

 

[Guest]

I'm answering both replies to-date and not just the one quoted below.

First, I understand the fact that Betas haven't always been optimized for
performance.

When I say "similar", I mean it's a direct backup of the same exact
database, made yesterday. In other words, the only difference is the fact
that two users are attempting to run the same database and that the database
I had problems with has about 10-20 more records in one table and 50-100 more
records in another.

And as far as comparing setups, I have used different versions of Access on
the same database before. In fact, I'm normally running Access 2003 on one
computer and Access 2002 on another, sometimes with three different people
accessing the database all at the same time without any noticeable
performance drop.

As far as my network -- I'm not IT, and definitely not a networking expert,
so I can't really give you technical specs, except to say that the network is
pretty stable and reliable. Both computers are using standard Ethernet
connections. Again, I have never before seen such a performance drop in even
opening a table in datasheet view. And even after the database is opened, the
GUI crawls.

Concerning ADPs: I've tried setting up Access Data Projects before, but
unless I'm missing something, they require an SQL server connection. And
while I know I can set up an SQL server on any computer (having done so
before), the problem there is that this database has to be very easily
portable in the event that we have to evacuate our facility and set up a
command post elsewhere (I work for a school district law enforcement agency).
And by portable, I mean we have to be able to put the database on removable
media and load it somewhere else, even if the network is down and we can't
access an SQL server. So, while MDBs may not be the most robust things in the
world, they're the best option for us that I know of.

I think I have what would meet the criteria of a persistent connection. I
went into the database without the usual front end (holding shift down to
bypass the startup options), but normally, the database has a bound form that
is always open if the database is open because of the way I set up the
startup options. I will try running the database with that form running
tomorrow and see what happens.

I suppose I should explain more fully the database, its usage, and my
problem. We have a single MDB file (yes, I know I should technically break it
up FE/BE, but as this is an internal application and it is frequently being
worked on, it isn't practical to redeploy a new front end each time I make a
change, even with the suggested solutions) running at all times, as we man a
24-7 communications center where we use the database to keep realtime records
of activity. At present, it is about 16MB in size. The dispatcher uses the
database with a set of forms, including a main form that is bound to two
tables within the database. Startup options are set so that this form appears
at startup and the database window is hidden. The user cannot access the
database window or any of the database objects directly except through the
forms. This application is run on Access 2003 with Windows 2000.

I presently administer the database. Until recently, I had been using Access
2002 with Windows XP Pro. I bypass the startup options so that I may work
directly with the database queries. I also use this database regularly on a
computer running Access 2003 with Windows XP Home (my personal computer). On
neither of these computers have I experienced performance decay unless
running an extensive query several queries deep using several tables. Unless
I have to change the design of an object, the dispatcher is running the same
database on his computer while I'm working. When I originally reported this
issue, I was working on the computer running Windows XP Home, except that I
was running Access 2007 Beta 2. When I opened the database (already in use by
the dispatcher down the hall), I was able to open the database (bypassing
startup options), but after opening one table in the database, the entire
Microsoft Access program began running slowly. The GUI was very sluggish and
was mostly unresponsive, even after it had finished loading the database and
the table. I even experienced great delay in navigating the data type
drop-down box on the table design view (not selecting a data type, but just
moving my mouse up and down the list), as well as navigating the Lookup
Wizard dialogs. After subsequently having Access shut down on me once and
having to be force-closed twice, I opened the database in Access 2003 and had
no issues. I then opened a backup of the same database (made yesterday),
stored in the same location, using Access 2007. This database had no
performance delays.

Carl

 

[Albert D.Kallal]

So, while MDBs may not be the most robust things in the
world, they're the best option for us that I know of.

They are *really* fantastic in this regards. You can read about my use of
this great feature also!!

http://blog.datamanagementsolutions.biz/2006/05/real-world-access-2.html

So, don't *ever* be afraid to use a mdb!!!

As for the performance probems, keep us informed!!

i