Speed Up a Backend - Front End Database.......???

[chrisbee]

I have a databse - backend approx 35000 reocords 50 fields per record.

The front end is stored on all the workstations that links to the Backend over the network - using simple linked tables.

How can I make this as quick as possible - would convertinf the table to SQL and linking that to the Front ends be any quicker?

Currently - even though all the relevent fields are indexed it takes too long (sometimes 5 seconds) to find a record (even without wildcards)

 

[SQLSister]

What is your front end and what is your current backend and how do you connect between them?

Questions about posting. See faq183-874

 

[chrisbee]

Hi

Both Access 2003 - all I did was split the tables out ont a seperate database on the server (which is a pretty quick dual Xeon) and link the F.E. database on the workstations

 

[Pikkunero]

IMHO it seems to me that the amount of data you have is just about so much that access can't handle it effectively any more.

Off course i'm not an MS Access expert but the experience i've had so far is suggests that you should be considering a change of Database.

Just my opinion. I may be wrong and if i am, could someone please correct me. I just may have to work with Access again one day

 

[chrisbee]

Pikkunero

I seriously hope not - its not the moveing the data that worries me - more the clever functions that Access uses to qualify the data etc.

I am sure (from the little I understand)- that SQL Server should solve the problem as it can deal with huge databases...I just need some pointers from someone who has experience with this problem.........

 

[RoyVidar]

Since it is a pure Access question, perhaps one of the Access fora would be more relevant? Access Other topic springs to mind (forum181). The topic has been discussed lots of times, and a search with relevant terms should provide some results. Here's couple of links that might be relevant (35000 records isn't that much really, unless the content per each record is large):

Microsoft Access Performance FAQ, Making Access Faster - 168 Tips to Speed Up Your Apps!, thread702-206410.

In short, deselect the Name Autocorrect thingie in Tools | Options, for all tables set the Subdatasheet Name property to None ([ignore][Auto][/ignore] is default), keep one table open against the backend at all times... (this and more info is found in the above links).

Roy-Vidar

 

[pweegar]

35000 records still shouldn't be a big drain on Access. It's built to handle tables of 2 Gig in size (I believe that's the number).

However, you do relaize that access will, depending on how you build your queries, download the ENTIRE table. And THAT can affect network performance a whole lot. You might want to think about moving to a more efficient database, such as SQL Server. Also the front end can make a difference. Not being familiar with Access 2003, does it support disconnected datasets thru ADO? Altho disconnected datasets have their own problems, they CAN improve performance, because once the user recieves data requested, the table is disconnected from the user until the user requests more data or makes a change or adds a record. Disconnected recordsets are the default with ADO.net (in vb.net and C#).

Moving to SQL Server SHOULD help your overall performance. Other things to look at are bottlenecks in your network itself. You can have the fastest queries in the world, the most efficient fron end, but if the network has problems, the speed and efficiency go out the window.

 

[chrisbee]

I appreciat ethat the network can be a problem -but 50% of the users are Remote Desktop - - so limited to the ability of the server and the conection - however - they currently have the best connection....all I THINK I want to do is link the main table through an SQL connection.....however I might be nieve in this assumption?