Database is slow or "Not Responding"

[jbeets]

Hello! I have a split database which is used by managers to complete audits on their teams each quarter. There are 8 tables each with a couple hundred records in them which contain the details by department for the auditsThere are also 8 forms which are based on the tables. The forms have some vba to filter the record selection so that the managers can select an audit for the team member that is appropriate to review.There are 3 queries for each table that summarizes the data for reporting purposesThe backend database is saved in a shared drive and each manager has a copy of the front end database which they saved to their desktop. Our managers are all working remotely as well.I have been googling solutions to help with the slowness of the database but so far I have come up with nothing. Sometimes the database will work relatively quickly and others you can click on a form, report or filter in the form and then walk away while it does its thing - It shows "Not Responding" at the top of the screen.Does anyone know if there is anything I can do or that I should change in order to make the database quicker?I was thinking that if I break the database down by department and have 3 databases instead of 1 big one that it might move faster however that's a lot of work and I thought I would ask if anyone thought that would help before I put in all of that effort and still have the same problem.... Thank you for any advice you can provide.

[Micron]

Remote connections can be slow at best and at worst, dangerous to the db. Depends on what you mean by that. Something like Terminal Services or Citrix is likely the best solution for speed problems, but if you're going through a vpn, then I doubt there's much you can do. Having said that, before engaging in tech savvy solutions, you might want to have your db checked out by someone who's familiar with good design concepts - or you could research db normalization. It sounds to me like you have a table per department and a form for each table, and that would be a bad way to do it. If so, it's kind of a red flag for all sorts of other things that you may have done, which can make the db overly large and clunky with no/too many indexes for example. You could also check out http://www.allenbrowne.com/QueryPerfIssue.html
One thing missing from your post is a clue as to how it performs when not being used remotely. Also, I hope that shared location is not on a cloud server. I would strongly advise against that.

[CJ_London]

This link gives an overview or working from home with access https://www.access-programmers.co.uk/forums/threads/working-from-home-with-an-access-database-over-vpn.313634/

[jbeets]

Thank you Micron! I appreciate the advice. So currently we are set up remotely using VPN. You are saying this could be a large part of the issue and is potentially why my backend database recently became corrupt?If I were to move the database into Citrix workspace it would help?I am basically learning as I go here and I really appreciate the link you provided.Thank you again for your help and advice!

[Micron]

Your post didn't say anything about corruption. That could be caused, among other things, by design issues or accessing the be on a cloud drive or user actions, such as killing Access from Task Manager while it's trying to do its thing. A VPN will slow you down so that will be a factor as long as it is in play. Question is, if that is the way it must be then all you can do is concentrate on the other potential db design issues assuming you must stay in Access. The only other option I know of is to use a web based database, and that you can no longer do with Access (AFAIK). This whole area of remote access isn't my expertise, which is why I stated that unless you are quite knowledgeable in Access design principles, you might want to either read up on the basics or have someone look at yours if you must stick with your current architecture.

I note that the link didn't provide any info on Citrix, just Terminal Services. My experience with TS is in a corporate setting where users had no pc, just a keyboard and something that looked like an android box. The statements made at the other forum wrt TS reflect what I know of it, but I can't tell if the writer was referring to anyone having TS at home. That's out of the realm of my experience. The TS I used was part of a wired LAN - it's just that most users didn't have a pc. If I had to access TS from home, I still had to use Citrix, so that's the breadth of my experience with those.

It would have been interesting to know what the writer had in mind with replication. Access hasn't supported replication since I think version 2010. He must have been on about a roll-your-own version, which I would avoid unless you have experience or access to such experience. There was a reason why replication was dumped, so I'm afraid I don't see the point in recreating that horse and buggy.

[jbeets]

Yeah, fortunately Tech Support was able to get a previous version of the database and only a few records were lost due to the corruption. Sadly, I don't have anyone that I know of that knows anything about Access. This forum has a been a life saver to me. I will do some major research on the proper database design and normalization. Hopefully that will help.Some of the stuff you said is a little worrisome to me though....if someone can corrupt the database simply by Killing Access whiles its doing it thing....I could have a lot of issues on my hand. I will have about 30 manager accessing this database to complete their audits and if doing something like that by one manager can corrupt the database I am not sure where to go from here....Again, thank you for taking your time to talk me through of all this! You are a rock star!

[Micron]

Thanks.
Can you use Task Scheduler on a pc that can be left running? We had one that was dedicated just to database backups; in fact, it was removed from the corporate list for automated logoffs and updates for that reason. So task scheduler can run a db via shortcut on a nightly basis and run a backup of the be. That way, you might only ever lose 1 days worth of data.

You might also consider staging tables for data that they are going to be updating but it wouldn't make sense to do staging in one table and updating in another in the same be file, because the speed problem will arise when they go to update. To use staging tables where they are in their own db and then update master tables in another file smells like replication but if it makes sense for how they are working with data, it might be a way to go, but not if they need to look at something that someone else created. You could never know if it was just changed an hour ago. The reason I likened replication to a horse and buggy is because of the other technologies that came along after - namely TS and Citrix to name just two.

EDIT - the thinking here being that someone would run an update while both the master tables and db doing the update are on a LAN and not to do updating across a VPN. As for the potential for corruption, in the meantime you must stress that no matter how long something seems to be taking, leave it alone - for a long time. The message that Access is not responding is misleading. It doesn't mean it has crashed; it means "I'm too busy to respond to your keystroke and mouse moves. Come back later." Using someone else's db, I have seen this unresponsiveness last for 20 or 30 minutes. To kill it is asking for trouble.

It might be possible that after doing research on normalization, naming practices, *table lookup fields, *storing calculations, *queries with DLookups, Option Explicit - to name a few - that you could post a sample db in a new thread for evaluation. However, you'd have to make it pretty clear as to what the db is for and explain the process it supports. If I were to do that, I'd write a treatise on the subject, then when I thought I was ready, I'd get someone who is good with technical writing to review it for a second opinion on clarity. Too often, too much jargon and ambiguity gets into such posts, or they are fraught with tons of unnecessary detail that just clouds the reader's mind. Expect questions for clarity regardless of how well you write it.

P.S. * denotes bad things.

[ssanfu]

RE: Corruption.

I hope none of the computers are connected using a wireless network connection. This is a known cause of corruption.

Albert D. Kallal wrote an article in 2003 about Using a wan with ms-access. When you get to the point of This network story gets worse!, (about half way down) pay close attention.


Again, this was written in 2003. Times have changed, but I feel it is still valid and worth knowing.


We didn't go with the TS or Citrix route. We expanded our server memory and hard drives (next expansion might be to SSDs) and created some VMs.

We created 4 VMs on our Windows 2012 server; we have a VM for the SQL Server Database and 3 VMs - one for each of us.
We use Remote Desktop to access our individual VM, which has an Access FE linked to the SQL Server BE. (Used to have an Access BE).
One guy has a wireless connection, but this isn't an issue because the user VM is on the same server as the SQL Server VM. The local computer can shutdown with no effect on the database because the access FE is on the VM. The only issue is the slowness of the wireless connection.

[CJ_London]

Citrix is a version of terminal server with additional bells and whistles. You connect to either from any device (including apple and linux machines) where the OS supports remote desktop - which is standard on windows machines - just click on the windows icon and type 'remote desktop'. So yes, you can connect to terminal server or citrix from home.

Note that ideally when working from home using VPN, users should be hardwired to the router, not using a wireless connection which a) may be slower tyhan a hardwired connected and b)introduces an additional link in the chain that might fail and cause corruption.

[Micron]

I don't know if we're talking about the same thing, or if I've called something by the wrong name, but as I mentioned, whatever it was, there was no PC, so no OS. Just a keyboard and a small box and you connected to a server. In the beginning, they discovered that because there were several servers, and you might end up logging on to a different one tomorrow, that some desktop settings/shortcuts would disappear. The had to ensure that a user's profile was on each server. AFAIK, it was called Terminal Services. If Citrix was involved, it wasn't obvious and you couldn't connect to these servers with a PC - or at least they didn't let us.

[CJ_London]

what was called Terminal Services was renamed as Terminal Server for the server end of things and Remote Desktop for the PC end of things. Your box probably ran Linux or it might just have been what used to be called a dumb terminal

[Micron]

I remember the word 'dumb' being used, but I'm not sure they were referring to the box.