Improve company productivity with a Business Account.Sign Up

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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