The Typical Progression of an Access Project for the Typical Company

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.