Optimization (1 Viewer)

Learn2010

Registered User.
Local time
Today, 11:38
Joined
Sep 15, 2010
Messages
415
The new IT group is attempting to move all Access DBs into one area on the shared drive. Since the process started several months ago, there has been a noticeable continuous slowing of one of my DBs. It is linked to a SQL Server. I have no permissions to do anything to the SQL Server. I have optimized Access as much as I can. Is there something I can do to speed up this Access DB?

Thank you.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:38
Joined
Jan 20, 2009
Messages
12,849
Can be many different things. Missing indexes are likely when queries start to slow down as the data grows. If you have views on the server they can benefit from just being forced to rebuild their statistics and plans when the data grows.

It is very difficult to make the most of the server without even being able to connect to it. See if your IT team will give you a login so you can at least look at the indexes on tables. You have to start worming your way in and you can't really break anything just looking.

I'm very lucky to be in a small shop where I am an SQL Server Admin too. It makes a big difference when you can actually build stuff on the server and use its tools to analyse and optimise queries.
 

Learn2010

Registered User.
Local time
Today, 11:38
Joined
Sep 15, 2010
Messages
415
There is no way. Access is frowned upon here and if they could get rid of it they would. Just recently they heard that Microsoft was getting rid of Access and it was sheer jubilation. I let them know about the SharePoint version that was being done away with and that the actual Microsoft Access software was here to stay. Some were dumbfounded.

Anything I do has to be on this end and through the Access software or if there is something I can do to the links then that would be all.

Thank you.
 

Minty

AWF VIP
Local time
Today, 15:38
Joined
Jul 26, 2013
Messages
10,354
If you are using a shared front end from a network location that will be slow.
And probably lead to longer term issues, such as corruption.

Each user should have the FE Access db locally on their PC.
 

Learn2010

Registered User.
Local time
Today, 11:38
Joined
Sep 15, 2010
Messages
415
There are only 3 users. Each has their own front-end and it resides on a shared drive as directed by the managers. I put front-end on my C drive and it does the same thing. I have compacted and repaired it and I use a module to turn off the subdatasheets.
 

Minty

AWF VIP
Local time
Today, 15:38
Joined
Jul 26, 2013
Messages
10,354
What type of network are you on Wired or Wireless?

The shared drive is nonsense, it's a FE with no data in it, why does it need to be on a shared drive? They clearly have no or incorrect knowledge about how Access works.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:38
Joined
Oct 29, 2018
Messages
21,358
Hi. You probably have seen this already but here it is anyway. Good luck!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:38
Joined
Feb 28, 2001
Messages
26,999
You may show this to your managers if you wish.

Before retirement, I worked at the Navy Enterprise Data Center New Orleans, one of a small number of Navy hosting sites. As we grew, we had 1200-1500 servers spread over 60-80 projects including some with either "Sensitive but Unclassified" (SBU) or Secret requirements. (It was not a Top Secret site, though.) ALL of our people had to have Secret clearances to even work on the in-house network, me included.

One of our biggest projects, data-wise, was from BUMED (U.S. Navy Bureau of Medicine), which used an Access Front End and an SQL Server back end. This project had to conform to both Privacy Act and HIPAA requirements - which it did. The FE files were on individual user machines in that environment. I estimate a user base of between 40 and 50 medical records clerks plus a couple of supervisors. Sorry, cannot legally tell you anything about the number of people or incidents that database actually tracked.

We were able to demonstrate that NO repeat NO sensitive data was ever stored in the FE files and that the ability of the user to print a report was a bigger risk than having an FE file on a workstation. The report ability was going to be a reality regardless of where the FE files were located.

It is an example of false security to force deployment of Access FE files in a way that risks damage to the back-end database. Stated another way, it is a violation of the principle of Operational Security to knowingly place a program in an at-risk environment if the risk includes damaging the data repository. The U.S. Navy saw fit to use a distributed Access front-end on sensitive personnel records subject to strict security requirements.
 

Users who are viewing this thread

Top Bottom