Solved

MS ACCESS SPLIT DATABASE SLOW

Posted on 2006-11-02
6
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 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