Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Speed in Access 2007 Multiple Connections

I once created a donor database in Microsoft Access 2003 for a non-profit organization. On problem we had was the speed after 3 users connected to the back end. They were always connected to the back end at all times.

I've recently started using Access 2007, and I am nearly finished with a project for a 300+ employee company to manage their engineering requests. I've never launched a live project with Access 2007, and I'm wondering if this connection issue was because of Access 2003 or if my tables were set up wrong.

My question is, how many concurrent connections can access 2007 handle without it bogging down? Let's say for this purpose that there are always 12 different terminals connected to one back end on a network, will performance suffer as users try to add records? I know there will be *some* performance loss from multiple connections, but not all the users will be entering records all the time. This is how my 2003 database worked also, but for whatever reason, the 2003 project seemed to slow down for everyone regardless if all of the users were actively using the database. It seemed as if just because users were conencted was enough to slow it down.

What should I expect from Access 2007? Anything different? How should I go about setting up the back end portion?

Thank you very much for your help
0
charlesguzman
Asked:
charlesguzman
  • 3
  • 2
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The theoretical max number of connections to a single Access db (any version) is 255.

How many you can *actually* connect simultaneously depends on a load of factors and variables.

Also, not directly related, but if you have the option to move to A2010 ... then you should.

mx
0
 
charlesguzmanAuthor Commented:
Thank you mx for your speedy reply,

I have one more related question. On the donor database that was slow, I just placed the back end on a shared drive (Mapped to a directory in Windows) on the network. Should I place the back end elsewhere or is it acceptable to simply place the back end anywhere that is shared on the network?

Thanks again for your help,

Charlie
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"or is it acceptable to simply place the back end anywhere that is shared on the network?"
No problem per se, but certainly on the fastest system would probably be desirable.  However, I highly recommend avoiding Mapped Drive letters.  Link the Front End master to the common back end using the Full UNC path to the physical drive/folder.

In theory at my company, all users are supposed to have the same mapped drive letter to our share drive, but that is *not* the case.

mx
0
 
charlesguzmanAuthor Commented:
Thanks for your help MX. You answered all my questions on this topic. I have another question I'm posting separately. Perhaps you could answer that one as well? I'm new here and noticed your credentials are quite impressive. I'm sure you would be able to help. Thanks again.

Charlie
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I would suggest spending some quality time reading the information contain within the following links, written by long time experts in the Access world. They pretty much cover everything I could possibly say.

100 Tips for Faster Microsoft Access Databases:
http://www.fmsinc.com/MicrosoftAccess/Performance.html

Ken Getz tips from Access 2002 Developer's Handbook:
http://msdn.microsoft.com/en-us/library/aa188211%28office.10%29.aspx 

Improve performance of an Access database
http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx

Microsoft Access Performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

mx
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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