Solved

MS ACCESS SPLIT DATABASE SLOW

Posted on 2006-11-02
6
651 Views
Last Modified: 2007-12-19
I recently split my ms access database onto a SQL server box.  Users have complained that it is  S..L..O..W.  Is there anything I can do to improve performance ?  (I tried upsizing to SQL Server first, but had too many table errors. Also many queries refer back to a form for its select criteria).  Any advice would be appreciated.  Thanks.
0
Comment
Question by:aappel5
6 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17864554
So your database *isn't* using SQL Server as its back end?
That failed - you're just using an mdb backend on the same phyiscal server upon which SQL Server runs?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 17864645
My first inclination is to suspect that you lost indexing when you split the DB. Make sure your tables have primary keys, make sure the foreign keys are indexed, and make sure you have indexes on other column combinations by which you access the tables.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 17865463
There are many things you can do to improve performance (indexing is key, as dqmq points out):

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

Also, PLEASE STOP POSTING IN ALL CAPS ... many experts refuse to answer questions posted in all caps.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17865538
And it makes reading it harder - as the brain recognizes word shapes rather than having to read each individual word, but that is throw out when everything is in caps.  (No shapes).
0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17873139
You might also try converting from an Access database to an Access PROJECT. It makes it cleaner keeping track of whether works is going on at the SQL-Server or in Access on the local PC.

The comments above on lost indexes and keys are where I'd start, too.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17873187
But don't become *dependant* on ADP's ;-)

Of course all of this is speculation until aappel5 actually comes back to clear some things up.
The issues if you persisted with the failed split to a SQL back end (i.e. you are in fact connected to a SQL Server database now) are different than if you abandoned it and are dealing with a Jet backend still (an mdb).

It *sounds* like it's SQL as you point out "queries refer back to a form for its select criteria" which is an absolutely classic way to make Server data fetches very innefficent using Access linked tables.

But you really need to give us some more info here!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question