RE: Database running over network runs extremely slow

45 seconds is a long time to search 3000 records.

The first question I would ask is: do you have indexes setup on the fields
you are searching (or using in your DLOOKUPs criteria parameter)? I would
guess not based on the speed of your processing. This is a necessity if you
really want to make your application scream.

The second thing I would ask is: do you have your database split into
Front-end (Forms, queries, reports) and Back-end (data only)? If not, you
need to do this, and put the front-end on each users machine.

The third question I would ask is: are you users running the application on
a LAN or a WAN? If over a WAN, I suggest you read this paper by Albert
Kallal (http://members.shaw.ca/AlbertKallal/Wan/Wans.html)

Another question (I've run into this lately): Are your users hard wired to
the network, or are they running over a wireless lan? This will also
contribute to slow processing in Access.

In your example, you indicate that your form is unbound, and that when a
particular type of data is read, it searches the database for data and
returns that info, using DLOOKUPs. When working with a split database over a
LAN, I prefer to use bound forms, but I give the users unbound controls in
the forms header or provide seperate search/filter forms to allow the user to
search for specific data. I'm sure that this is faster than retreiving a
bunch of data using DLOOKUP statements.

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

email address is invalid
Please reply to newsgroup only.



"Nicholas Scarpinato" wrote:

Hello. I have an Access 2007 database that is currently used by three to five
people. I have an unbound entry form that is used to enter data, and on this
form a lot of the data that is entered is parsed via code. The code takes the
entered data, looks to see if that data is contained in various tables
(depending on what piece of data is entered), and then displays the data in
those tables. For example, when a barcode of a SKU is scanned, the database
finds the SKU in the Item Numbers table and displays the description and the
part number. The problem I'm having is that this searching process can take
up to 45 seconds to search a 3000 record table with four fields. Most of the
searching is done with Dlookups, but I'm converting some of it back to looped
recordset searches because they seem to be running a lot faster. So I guess
my question is, does anyone have some tips to help me improve the performance
of this database? Are looped recordset searches going to be faster than
DLookup searches over a linked database on a network?
.

Relevant Pages
Loading