Question Speeding up and Access Database

[pbrydone]

I have created an MS Access database primarily for tracking file movement. The database is a split database and has a front end which sits on client computers (about 10 computers) and a back end which sits on a Server 2003 server. The problem I am having is that the database runs too slow even with no more than 2 concurrent users. I have checked our etwork and can't spot any issues that culd slow things down. On a standalone non-split database it appears to run quite ok. The tracking number is scanned in via a bar code scanner and other fields are manually entered.

Grateful for advice on how I can speed things up and also if anyone has some ideas on how to generate a search form.

I have attached a non-split database in zipped fomat

Many thanks

 

[PaulO]

Regarding your table structure in tblTracking you have made provision for up to five movements ... is that an absolute maximum?


Regardless of that answer, strictly speaking you should split this table between Header (Parent) and Detail (Child) records.

1. The Header table would contain only the PACKAGE data/info
   • Ref/Code
   • Full Description
   • etc
2. The Child table, structured correctly, would then be able to record UNLIMITED movements
   • Date In
   • In From
   • etc
3. Ideally you perhaps want to set up a blank form and build two subforms, the first sourcing the Header Table data and the second sourcing the Detail Table data
4. This will enable you to view and access multiple sets of data on one screen.

Hope some of this makes sense?

 

[pbrydone]

PaulO - Many thanks for this. I must admit I'm lost. Grateful for any more help that you could give please.

 

[PaulO]

Is the existing data in that table required to be kept?

 

[pbrydone]

PaulO - The data is exactly what will be entered and kept in the the same format.

 

[pbrydone]

PaulO - the data is real and will be kept.

 

[PaulO]

Load form frmDISPLAY

This form allows you to add NEW entries as well as REVIEW existing records.

 

[pbrydone]

PaulO - Many thanks for the doctored database but unfortunately I need the data enry screen to stay the same layout as I have it so that users can enter the Tracking Number via a bar code scanner. Grateful for any other ideas that you might have.

 

[motleyjew]

One suggestion that helped me out was to give the backened a short name and place the backened as close to the root as possible. So instead of putting it in P:Folder/2ndFolder/3rdFolder, Put it in P:Folder. I can't tell you how much this helped one of my databases. Aslo, here is a link that has some suggestions of increasing speed:

http://www.blueclaw-db.com/access_consultant_rapid.htm


Good luck
Gregg

 

[Simon_MT]

I agree with Greg, put the database as far up the Server's directory tree as possible. If the server has RAID and/or multiple volumns put the database on the smaller volumn rather than a large volumn.

On the Backend create a Table (can be one record), I created a Table called companies and put all the default information relating to each company. Create a FE Query with a result of one record and put this behind the frmMain.

Simon

Simon