Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have had a consultant install a database for a client that is based on Access 2007 with a front end (on each workstation) and I guess the database back end (on the file server, it is a server 2003 domain controller with very current hardware, memory etc...).

When we launch the database from the workstation we get our typical log in screen, when a user logs in and there is only one user on the database interface the whole thing flies really quickly, keystrokes are very very quick etc.

As soon as another user logs into the database interface or program, everything crawls to a slow latent halt. Keystrokes in field boxes take forever to appear, for example if you type in the word "find" the f would appear then about 10 seconds later the "i" would appear, then 10 seconds later "n" then "d".

The current company that sold the database to us is suggesting a trial and error approach and I am not interested in hacking the registry on the server just to see if it makes a difference.

I am in desperate need of help as I don't have time or interest in databases, please all suggestions are welcome at this point.

Just to note, the network has no issues from cabling to switches to other connections. Using any of the workstations for file server browsing and opening files, email, internet etc all work extremely quickly. The only thing that stops working right is the database.

share|improve this question
    
whats the extension of the access file on the clients? is it adp, ade etc.. Also does this thing use ODBC or not. –  tony roth Jul 20 '10 at 22:21
    
i believe odbc was installed, it was mentioned in a previous email. the front end extension i will have to wait till tomorrow to get that. –  user8256 Jul 20 '10 at 22:27
    
if it uses odbc then we might be able to get somewhere let me know what you see in the odbc control panel –  tony roth Jul 21 '10 at 1:04
    
@tony roth, it seems that there is no odbc settings, it seems that each workstation just has a shortcut to the server location or file for the database which is a .mdb shortcut. If the db was called mydb.mdb then the shortcut is on the desktop for mydb.mdb using unc path back to the shared resource location on server 2003. –  user8256 Jul 21 '10 at 3:34
1  
@dasko, I'd put netmon 3.4 on a client and watch what happens between key strokes. Does it send a new query on every key stroke? –  tony roth Jul 21 '10 at 14:46
show 2 more comments

3 Answers

up vote 1 down vote accepted

Welcome to Microsoft Access-based databases, and shared-file database "technology" in general. Thankfully, the problems today mainly relate to speed (whereas, with prior Windows OS versions out-right data corruption could occur).

You're seeing the immense slowdown because the server has to be a lot more careful about granting locks to clients when more than one client accesses the same database file. When only a single client is accessing a file the server can allow the client to obtain "opportunistic locks" and cache more of the data locally. Multiple clients makes the situation much more stringent for the server computer because consistency amongst the clients with respect to the apparent contents of the file must be maintained.

You may be able to gain some incremental performance by playing around with settings in the "Server Service" (LanManServer) on the server computer, but you're never going to see the same performance with multiple users as you would with a single-user.

If you really want to see this perform well I'd recommend evaluating the possibility of "upsizing" the back-end database into SQL Express or some other true client/server database engine. If the "back end" is just a collection of tables that the "front end" is using "link tables" functionality to access then it's a pretty good candidate for moving to an SQL Express instance. The consultant wouldn't have to do much more than use the SQL Server Migration Asstant for Microsoft Access.

Besides being client / server and, thus, much faster, SQL Express is also going to get you a nice way to take online backups (while users are actively using the database). Users can "forget" to close the application and you'll still get backups, whereas, with Access, the back-end file will be "locked" and unavailable for backup.

The list of advantages you'd get from using SQL Server / SQL Express on the back-end are too long to lay out here, though I suspect that your consultant isn't familiar with them since they didn't bother to use SQL Server / SQL Express to begin with. You may want to consider getting another consultant if you decide to expand / enhance the application later on.

share|improve this answer
1  
Upsizing an app to SQL Server can require more work than just relinking the tables. Your comment about prior Windows versions causing corruption is because of perforamnce settings, commonly called OpLocks, that MS introduced in Windows Server. Once the Access product group were told of this problem they rapidly figured out what was all going on. So your comment blaming Access is wrong. –  Tony Toews Aug 25 '10 at 3:04
    
@Tony Toews: In some cases, though, upsizing an application that's already being used in a "front end MDB" with "linked tables" to a "back-end MDB" is a pretty simple "move the data tables into SQL Server and link the tables in the front end to the SQL Server" operation. It just depends, but it can be fairly easy, too. I don't get where I'm "blaming Access" in my answer. In the 1st parapgraph I mentioned that data corruption can occur in prior Windows versions, but that mostly problems today relate to speed. I'm not "blaming" anybody-- "shared file database" is a bad idea, period. –  Evan Anderson Aug 25 '10 at 11:47
1  
@Evan Anderson: you really don't know what you're talking about if you think upsizing is just a matter of changing the linked tables to point to your SQL Server after you've imported the data tables. It takes careful evaluation of the app after the upsizing for a whole host of potential problems and bottlenecks. And it's not at all unusual for an upsized Access app to be SLOWER than the original. –  David W. Fenton Aug 30 '10 at 18:24
2  
@David W. Fenton: There's nothing I want to debate here. Shared file database is a bad idea (doesn't scale, works poorly over low bandwidth or high latency connections) and it's not at all a forward-looking technology. Given that there are many no-cost and free/open source client/server RDBMS offerings (including SQL Express, from Microsoft), trying to develop an application of any scale on a shared file database is a poor technical choice. No doubt Access has done well for you in your work, but it doesn't compare to a real RDMS (SQL Server, Oracle, Postgres, etc) in terms of scaling. –  Evan Anderson Aug 31 '10 at 15:00
1  
If you think something like SQL Server Express is an easy replacement for a Jet/ACE back end, then you really need to get out more. There are a whole host of complexities involved that make a Jet/ACE very attractive for a lot of environments. And Jet/ACE is a real RDBMS, just not a server database. And it scales just fine for the user populations it was designed to serve. –  David W. Fenton Sep 1 '10 at 3:06
show 1 more comment

My apologies for not reviewing Server Fault much for MS Access questions.

1) This is a well known problem with a simple solution within the MS Access community. I doubt the the technical competence of the consultant or developer of the Access application if they are unaware of the problem and solution.

2) See the Access Performance FAQ page which I have maintained since about 1997 or so. In particular the first link about the LDB locking will solve the problem with the second user making the app slow.

share|improve this answer
add comment

You may want to install SQL Server Express, which is free and has a 10GB database size limit -- substantially bigger than Access -- on the DC and link the individual tables to the Access front-end through ODBC. You'll likely get much better performance this way for multiple users, and Access can be a network hog even for 1 user the way you're currently set up.

share|improve this answer
2  
stupid consultant should have figured that out before hand. –  tony roth Jul 20 '10 at 22:19
    
trust me i have already opened a ticket with them on sql express 2005, there is no budging on this right now, this is what they have to work with so we have to try to at least accomodate that before i push them into another direction. –  user8256 Jul 20 '10 at 22:21
    
Yeah, there's probably a good living to be made cleaning up just these kinds of setups created by "consultants" and in-house "experts." –  nedm Jul 20 '10 at 22:25
    
@dasko, SQL Express 2008 R2 is the current version, and is still free but allows for bigger database sizes (up to 10GB) to go along with some other bells and whistles. –  nedm Jul 20 '10 at 22:27
    
@dasko I'm assuming the consultant was told that it was going to be multiuser right? –  tony roth Jul 20 '10 at 22:27
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.