The Typical Progression of an Access Project for the Typical
When there is a task that takes a length of time to complete, it
is common for a power user to create some fancy Excel spreadsheets using things such as 'vlookups' on
imported data. This type of automation saves time and money for the company. But sometimes the task
that was automated begins to grow--and Excel can no longer practically handle it.
At this point, the next logical step is a relational database such as Microsoft Access. The power user
will take it upon themselves to learn Access and convert the Excel spreadsheet to Access. Many this
times will suffice and this Access automation will meet all the needs.
Some of these Access databases developed by the power user become popular and people from inside and
outside the department find it useful. These new users will usually start asking for more
functionality in the database.
At this point, this upward path hits a dead end. This dead end is not due to the limitations of
Access, but from poor database design. With Access, there is a learning barrier in relational
databases that is hard to break through.
There are a few options at this point. One would be to try to find some off the shelf software that
might do the job. Another would be to send the power user off for some intense training in hopes they
could redesign the database. The other alternative would be to hire custom software companies like
Affordable Automation to come in and give a recommendation. All three alternatives are definite
possibilities and should all be considered.
If the decision in some shape or form continues to use Access, its size limitations will likely not be
an issue at first. Access has a 2 gigabyte size limitation which is quite large for most tasks. We
have developed databases with over a million records that still function quite well today. Another
limitation that might be reached is too many of concurrent users wreaking havoc on performance. Even
if the database is being used from a server shared network drive, all the processing still happens on
the desktop. So when a user makes a request, an enormous amount of the data is pulled over the network
wire every single request. Since Access is a file based system and not a client-server database, these
limitations pop up.
But, if one of the limitations happen to be reached, there is still another quite easy alternative.
Simply move the Access tables to Sql Server and link Access to Sql Server. Sql Server Express Edition,
which is offered as a free product by Microsoft, has a 10 giga-byte limitation. Even if this 10
giga-byte limit is not enough, Sql Server has other editions that are reasonable in price.
This Access - Sql Server combination in many ways acts as a client-server solution. When a user makes
the request from their desktop in Access, the processing now takes place on the server and only the
results are brought over the network wire.
Whatever point your company happens to be in this process, give us a call at (513) 442-9210 to discuss
and work through the best route for your database.