2

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.

6
  • 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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 14:46

4 Answers 4

1

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.

6
  • 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, 2010 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. Aug 25, 2010 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. Aug 30, 2010 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. Aug 31, 2010 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. Sep 1, 2010 at 3:06
3

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.

1

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.

12
  • 2
    stupid consultant should have figured that out before hand.
    – tony roth
    Jul 20, 2010 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, 2010 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, 2010 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, 2010 at 22:27
  • @dasko I'm assuming the consultant was told that it was going to be multiuser right?
    – tony roth
    Jul 20, 2010 at 22:27
0

This happened to me too But I resolved it 1-create a blank database. 2- goto external data from menu bar. 3- from import and link click access to import. 4- find your backend and click on it. 5- import all tables on you backend. 6- rename you new backend as old backend. 7- link the frontend from all user to new backend.

100 work

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .