Solved

Outgrowing Microsoft Access, what next?

Posted on 2008-11-03
19
280 Views
Last Modified: 2009-01-23
We have a microsoft access database which everyone in our company uses (70 or so users).  Basically what's happening is that people are inputting data into this thing at a rate which is cause the size to double basically every month (the size is now 16 megs).  This of course isn't sustainable since we're already experiencing slowdowns and sluggish behavior.  We were wondering if hosting the database on a dedicated server would expedite things, or is that just putting a band-aid on things, or will that have little effect?  I don't know much about SQL server, but is that the solution to our problem or is that overkill?  Please let me know, looking for general insights on solving this issue.  Thanks.
0
Comment
Question by:LB1234
  • 6
  • 5
  • 2
  • +5
19 Comments
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
Hi LB1234,

Check out the following link
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Good Luck!

Gary
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Hmmm, provides some insights into problems with upgrading to SQL, but I'm looking to know whether that's the solution in the first place.  Thanks anyhoo.
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
LB1234,

I haven't been able to read the previous suggested link in full, so apologize for duplication; however, IMHO your next move is to MS SQL Server 2008 Express Edition (MSDE -- but may as well use newer technology) which has a limit of 2GB and is the free version of the MS SQL Server platform.

The express edition will allow you to see how the transition will be to MS SQL without big costs as it is free.  The only costs involved will be the soft dollars of your learning and r&d of migrating your application.

I suggested that as your development team is probably use to VB / T-SQL the Microsoft way having worked with MS ACCESS, but alternative is MySQL 5.x+ as it is also free; however, the learning curve may be more and the changes to front-end may need more work.

In the transition period you can have your frontend in MS ACCESS and the backend in SQL Server.  As time progresses you can add a more rich frontend using .NET which again you can enter in for free with downloads of .NET redistributable and Visual Studio Express Edition(s).
0
 
LVL 8

Expert Comment

by:rseabird
Comment Utility
I don't know the exact configuration. Normally, if we are working with Access applications, we use to Access files, one front-end and one back-end. In this situation it is quit easy to move the back-end database to a SQL server enviroment.
It seems, that running e.g. queries, the Access file size expands a lot. You need to compact the database regularly. (It is also possible to compact a 'remote' access database file, e.g. from the front-end database, compact an other access database file).
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<< This of course isn't sustainable since we're already experiencing slowdowns and sluggish behavior. >>

  First, 16mb is really nothing.  The database size limit for JET is 2GB.  You should have quite a ways to go before you'd start have problems.    As for performance, make sure your compacting on a regular basis.  I'd also look at indexing as a first step.  Over indexing is just as bad as under indexing, especially in a heavy add/update environment.

<<We were wondering if hosting the database on a dedicated server would expedite things, or is that just putting a band-aid on things, or will that have little effect?  >>

   If by dedicated, you mean to just support the database, no, it won't make much difference.  Access with a JET backend is all client based; all the processing is occuring on the clients.  The server is just acting as a file share.

  There are things you can do to make the server more efficent though.  See:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/kb/303528

<< I don't know much about SQL server, but is that the solution to our problem or is that overkill?  Please let me know, looking for general insights on solving this issue.  Thanks.>>

  I think this is something you'd want to look at.  With a SQL Server backend, processing could be moved server side.  This is important as it sounds to me like either:

A. The app is not written well for performance.

B. You have network congestion.

  WIth 70 users, my guess is it's B, with a little of A thrown in.  Apps that grow fast don't get thought through as they should starting off with a handfull of users.  Performance issues are not taken into consideration as they should be up front.  App works well then to a point, but when you hit a certain number of users, it goes downhill fast.  This also causes B to a certain extent.

  Also the number of users is an issue.  Typically about 35 - 40 users is tops for a JET based read/write app because of corruption problems.  With any database, an abnormal disconnect can cause corruption.  Since JET is client based, you have 35-40 users each processing against the DB file rather then one centeral engine server side that can roll things back if something happens as you do with SQL Server.  So when you start getting up into this range, the odds increase to the point where sooner or later, one of those users is going to cause a problem.

  I'm surprised that your not having problems already with the 70 users.

  SQL Server is also more robust and offers more features, such as on-line backups, rollback and forward capabilities, and processing server side.

  JET apps can be pushed farther then that if you have a rock solid network, well trained users (no Ctrl/Alt/Del), and a well written app, but with 70 users, I would really look at another backend database engine.

FWIW,
JimD.


0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

LOL, looks like I took too long to type that response<g>.

JimD.
0
 
LVL 28

Expert Comment

by:chilternPC
Comment Utility
No not overkill, MS SQL would be the easiest upgrade path but
what's more worrying is the growth rate - double each month? so its been going 4 or so months? and this time next year it will be 32Gig ? or do you think the growth rate will fall?
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
mwvisa1,

So this is a free, scaled down version of SQL Server?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:LB1234
Comment Utility
Jdett,

Thanks for the wealth of information!  Really helped a lot.  What is JET though?  And how do we know if our database is JET?

And actually, we use Access Runtime on our client machines, users do not have a full copy of Access on their databases.  Could this be factoring into the issue?
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Jdett,

the following was from the link you sent (with my comments below the drawn line):

Use a robust file server
Make sure that you have a robust file server that can handle the number of users and the requests that are being made to the Microsoft Jet database file. Additionally, make sure that the file server is not overtaxed with handling other processes, such as acting as a Windows domain controller, an Exchange server, or a SQL Server.

A problem also occurs if you restart the server to fix a problem with another important service, such as the mail service. Problems also occur when you restart the server after you apply new software or you apply a service pack or a hotfix, and you forget that the Microsoft Jet database is currently shared on the server. When the file server is restarted, unexpected interruption of the client connections to the database occur. This may cause database corruption. To prevent interrupted client connection, all clients must close the database before the file server is restarted or before software updates to the file server are applied.

A file server must also be put in a secured location where the file server cannot be accidentally switched off. The server must have an uninterrupted power supply (UPS) to help protect from intermittent power outages or from power fluctuations. The network file server must also have the following:
"      High-performance hard drives
"      A high-quality network card
"      Lots of RAM to make sure that the server can handle the load


_________________________________________________________

The database files on the backend are currently on a domain controller which is also our file and print server, so that server is doing quite a bit.  I wonder if this factors into the problem, although file and print requests are served up pretty quickly.
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's trying to make it so that things aren't corrupted?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Thanks for the wealth of information! Really helped a lot. What is JET though? And how do we know if our database is JET?>>

JET is the default database engine used by Access. Your database's would have the extention .MDB, .MDE, or .MDW (although actually they can have any extention if you want).

JET is a departmental level client side database engine, which is designed to be used over a local LAN.

<<And actually, we use Access Runtime on our client machines, users do not have a full copy of Access on their databases. Could this be factoring into the issue?>>

No. Actually, the "runtime" and the full version are the exact same .EXE. Only difference in the installs is some registry keys. When in "runtime" mode, all design features are disabled. In fact you can put the full version in runtime mode by using the /runtime switch from the command line.

<<The database files on the backend are currently on a domain controller which is also our file and print server, so that server is doing quite a bit. I wonder if this factors into the problem, although file and print requests are served up pretty quickly.>>

Yes, that does sound like a lot. I would use task manager on the server to monitor Memory, CPU Load, and network utilization when everyone is going at it. If you see bottle necks in any of those, then moving to another server would help.

If you don't, then it's probably congestion out on the network that is slowing things down. and hopefully, none of your users are accessing the back end over a WAN. I'm assuming yourt app is split, with a front end on each station and a back end with data only on the server. If not, then this is something you need to do as without doing so, not only is data getting pulled over the network, but all the objects (forms, report, code, etc) as well. That just makes network congestion that much worse.

JimD.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's trying to make it so that things aren't corrupted?>>

  No, that is actually a good design.  Its called a "split" design and is important to do.  Although there are *simple* ways to make the rollout of a new release automatic.

  So what size is the backend with all the data that is sitting on the server?

  The situation sounds quite a bit different then what I thought it was.

JimD.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
LB1234 said:
>>We hired an Access person to come in here and set this all up for us.   Basically every person in the firm has
>>their own folder, with a copy of the 16 meg front end in it.  Every time she makes updates to the file she has
>>to copy the 16 meg access file to all of our users.  Seems like a bizarre way to do things, but I guess she's
>>trying to make it so that things aren't corrupted?

That does not sound unusual: best practice dictates that each user have his/her own copy of the front-end.  There
are tools available that make this front-end update and distribution easier and more automatic, but they are not
free.

Based on having 70 users, I wholeheartedly concur with the other Experts that you need to think about moving to
a more robust RDBMS for the back-end.  SQL Server is probably easiest given that both are MS products, but
any enterprise-class RDBMS (Oracle, DB2, SQL Server, MySQL, others) will do.

Please note, though, that to really get the benefits of the change, your app may have to be substantially rewritten,
to ensure that most of the work gets done on the DB server and not on the client running the front-end.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
LB1234,

Yes, SQL Server Express Edition is the free version of SQL.

Although it has the same limit per database of 2GB, you can have multiple databases and gain all the other robust features of SQL Server.  There are very few things that they left out of Express Edition especially if loaded with Advanced Services.

Think it will allow to do more tuning of performance and pruning on some scheduled basis to keep backend size manageable.  Since you can have stored procedures and other such code, the size of the front end for queries may descrease as well.

My $0.02 anyway.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Although it has the same limit per database of 2GB, you can have multiple databases and gain all the other robust features of SQL Server.>>

  Just to be clear on this, multiple databases apply to JET as well to get around the 2GB limit (you can have multiple backend databases each with a single table of 2GB).  What's important is the features that SQL Server and other RDBMs bring to the table.

  Also, as far as moving to SQL Server, you can do it in a couple of steps.  First would be simply to move the tables into SQL server and then use ODBC links in your Access front end to connect to those tables.

  What you pickup is on-line backups, a more robust situation in terms of corruption (now only one engine is touching the data, which runs on the server), and in most cases, some speed.  This can be achieved with re-writting very little of the app.

  Phase II would be to start using pass-through queries.  It's just what it sounds like; you write a query in the app that gets passed through to the server.  JET doesn't process it locally (JET does stay involved even though your using a SQL Server backend unless you take some extra steps, but no where near to the same degree as if you were using it on it's own).

  Because it is passed through and executed server side, the SQL must be in the dialect of the RDBMS your using.  That requires some changes in your SQL other then just making the query pass-through.

  Phase III is the use of stored procedures and triggers to carry out actions, enforce business rules, etc.  All this happens server side, so it's very fast.

  But Phase II and III require significant re-work of the app.  Most will stay with just moving data into SQL and are happy with that.

  As has been said, SQL Express is free, so you can try that out without much effort and see if it will work for you.

JimD.
0
 
LVL 1

Author Comment

by:LB1234
Comment Utility
Ok guys, checking a few things regarding.  Will be sure to award points tomorrow for all the very helpful responses.  THanks again.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You forgot to answer the clever question from Jim:

>  So what size is the backend with all the data that is sitting on the server?

>  The situation sounds quite a bit different then what I thought it was.

/gustav
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now