Multiple User Limitations

[Nosaj08]

Hey Everyone,
Over the past 6-8 months, I have been assigned the project of building a rather large database for our department. This database is split into two seperate databases, with the tables residing on a network shared drive and the menu system running locally on the machine. The problem that I am running into is as soon as we store the linked files on the network drive, the database bogs down because there the network drive is slow in general due to the amount of users accessing it all day. However, when the link tables are stored locally, or on a flash drive, the database has no performance issues at all.

I made the suggestion to get our own dedicated server to house this database to help eliminate the problem, however, the tech department says the problem is Access in general and unless I adopt a different program for my database, I will always have performance issues with multiple users. Does anyone know if this is a true statement? I currently only have about 3-5 users logged in at any one time but would like to increase the amount to about 30-40.

Quick note: The database is slow only when the linked tables are located on the network drive, even if only 1 user is logged in. Also, I have been through the performance threads on this forum and have taken all mentioned suggestions for increasing the performance of linked tables into consideration.

Thanks for the help in advance!
Jason

[RuralGuy]

The best article I've found on MultiUser Jet systems:
http://www.accessmvp.com/TWickerath/.../multiuser.htm

Great link for performance issues:
http://www.granite.ab.ca/access/performancefaq.htm

30-40 users is probably pushing Access and Jet past reality. You might want to look into SQL Server right away.

[NTC]

its doubtful the 'tech department' knows enough about database architecture to make that broad statement. and what db do they suggest?...sqlserver?

a couple of things:
1. put your BE file on just another PC on your network that has a shared folder that you can see from your desktop PC - and link to that BE for a trial - this eliminates the server - that way you can make a time trial of it using the network but not using the server. Clear & organized time trials is the only way to approach any tech department - and so you can clearly show what the network + server are introducing in terms of delay.
2. the network equipment makers (Cisco) have implemented a QoS feature (Quality of Service) to prioritize applications - - this is so that your database is not waiting while people view last nite's Colbert Report at youtube with their browser....see if your tech department can implement that...

bottom line is you could spend alot of time and alot of money moving to sqlserver - and the delay will be exactly the same.

[Rainlover]

I can have 100 - 150 logged in at the same time.

Yes it is a little slow but then so is everything else.

This is not an Access issue unless your code is not optimised.

[Nosaj08]

Hey Guys,
Thanks for the information! Ill probably read through those links sometime today and also run a test with the database located on another machine. It is somewhat difficult to do these types of tests with how hard the techs have this place locked down but it should be able to be done. Thanks again for all your input and Ill let you know what I find out or what solution I arrive at.

Jason

[RuralGuy]

Thank you Jason. We would all like to see your results posted.

[Nosaj08]

Hey Everyone,
So over time, I have been running various tests, reading through the articles posted above and double checking to make sure everything listed in those articles was included in my database to ensure optimization of the performance. Well, today, I think I hit the nail on the head and found out what was causing the problem.

In the second article posted above, thanks RuralGuy, they mention that it could be caused by network security. Because the tech department is extremely strict at this company, they have permissions ran on pretty much every level of the folder hierarchy. I noticed this as I was browsing through the network today that as I got deeper into the hierarchy, everything would run slower until navigation eventually came to a crawl.

So, to test this option, I moved the database from 8 levels deep to 4 levels deep and it was like day an night. However, because I do not have permission to write data any closer to the root directory, I am going to have the tech department set me up with a folder in the root directory that has the sole purpose of housing the database. Once I get this done and go through the process of transferring everything over, I will update you all on the outcome to confirm this was the issue. Thanks for all of your help!

Jason

[RuralGuy]

Outstanding Jason! Thanks for the update. I'll add that to my bag of tricks.

[Rainlover]

Very good news.

Keeping your Folder High is also helpfull without permission problems. No wonder you were having problems.

[Nosaj08]

Yeah, unfortunately, I wanted to put it closer to the root but the way the company's network is structured, everyone is limited to their department's folder location. If I can prove that there is a large difference in performance, then I can make the business case to have IT lax on their standards and make an exception. Especially because some of the execs will be using it, they don't like to wait around!

[maximus]

I would like to suggest it is high time you migrated to some thing better. I have over 8 years of experience of working with Access and believe me although its very easy to use, very user friendly it has its limitations. Splitting your database into BE and FE is the solution that every one will offer, although it improves performance but not considerably.

I moved onto Mysql and PostgreSql both free and excellent RDBMS. Try it out its great.

[RuralGuy]

Actually I do not believe splitting a database improves performance and I have not seen it suggested as a performance solution. In fact there is pretty good evidence that quite the opposite is true.

[Microsoft Access Database]

MS Access does not scale well for that many users. You would be better using SQL Server Express which is free.