We are using MS Access for a large project at work and probably have 6-8 people working on it at the same time.
We have done this in the past with as many as 40 people using it, but this time around, the damn thing is slowing to molasses every couple of hours. The only remedy seems to be having everyone log off and I repair and compact the dbase and have them log back on.
Also, it seems the company here has only ONE server, and rumor has it that that server is getting pretty full. However, our part time IT person claims that shouldn’t be the problem with MS Access and he seems to think I have a corrupt program.
So…anyone have any idea what the problem could be, and any suggestions as to how to fix it?
I also back up the dbase at least twice a day on my local C drive as I don’t trust the server (where it is backed up only once, at night). Thus, it would not be a problem to go back to an earlier version, if you think that would solve the problem.
Again, in 4 years we have not had this problem. Sever or “corrupt” dbase problem? Help!
There could be network collisions that are not being resolved, resulting in open sessions that eventually corrupt the dbase.
If network traffic has increased considerably, putting a strain on the server, this could cause dbase corruption.
If you’re using a FE/BE configuration, the number of users pounding on the FE could be a problem. A solution to that is to create an FE for each user, either resident on the server or on their workstation.
I’ve got apps that have 20-30 users that seems never to break. I have others with 5-6 users that break every other day. Some developers claim that the only way to create a bullet proof app is to use all unbound forms.
Your app may be corrupted. Have you tried the /decompile switch?
What’s the traffic like on the server? Are you trying to use one box as your SMTP, POP, web, printer, and general file server as well as being the workgroup’s Access server? Multitasking is nice and a good serverOS is designed for it, but there’s still only so much throughput potential in those pipes, and while the processors aren’t generally going to be flooded, the same is not true for the poor little read-write heads on the hard disks. So while it may indeed be corruption, it may be that your server is being asked to do too much simultaneously – the email traffic alone may have dectupled or worse since the time that you had 40 users using this system with decent performance, thanks to the spam explosion and virus traffic.
Meanwhile, if you got decent and continuous performance with 40 concurrent users, you were doing pretty damn good for an Access system. That sounds more unusual than experiencing it being slow as molasses!
Unlike Filemaker (the geeks of which have tended to regard Access as its primary competitor in the small-business / workgroup-sized environment), Access does not scale all that well. It does a bit better than a shared Excel workbook but not by a whole hell of a lot. FileMaker will handle a couple hundred concurrent users of a hundred or so tables without breaking a sweat. But that’s when you give FileMaker Server a dedicated server box to run on. Even FileMaker will crawl if you try to run Server on the same box as a boatload of other processes. So that’s the first thing I’d try.
What most Access geeks do when they hit scaling problems is write a back end in SQL – and there’s a sort of built-in mechanism for generating a set of SQL tables from an Access database, although I’ve heard that the result isn’t what you would have generated if you’d set out to create a SQL solution from the outset. (Or, alternatively, you can do a back end in Oracle, another big iron multiuser system) Then you use Access as the front end – the GUI and the container within which to display and format “reports” and whatnot. That’s where you’d see your real performance increases – split the table structure itself off into a dedicated SQL or Oracle structure.
According to the specifications published in the help file the maximum number of users is 255, but I wouldn’t ever do it.
As to the db being slow I agree that the first thing you need to do is split it so that only the data is on the server (back end) and all of the application stuff, like the forms and reports are on each individuals front end.
Also, if you have not done so in a while, repair and compact the db.
For more info try utteraccess.com, it a GREAT site with TONS of help!
I would consider upgrading the database to the Microsoft Desktop Engine (MSDE). MSDE is a free version of MS SQL Server, designed for applications with a small number of users. You can download it here.
There are utilities available which will migrate your data from Access into SQL Server/MSDE.
MSDE has throttling that prevents more than 5 (or 10, I forget which) simultaneous connections.
Now in a FE/BE arrangement, those 5/10 MSDE connection slots could support a lot more Access users, IF Access is smart abut connecting/disconnecting from the back end store. I don’t do enough Access work to know the definitive answer to that one.