Solved

MS ACCESS SPLIT DATABASE SLOW

Posted on 2006-11-02
6
677 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 85

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
Independent Software Vendors: 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

628 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