[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 692
  • Last Modified:

MS ACCESS SPLIT DATABASE SLOW

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
aappel5
Asked:
aappel5
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
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
 
dqmqCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Leigh PurvisDatabase DeveloperCommented:
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
 
GordonPrinceCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now