The Typical Automation Path for the Typical Company:
When there is a task that takes some time to complete, many times a power user will create some fancy Excel spreadsheets using such things as "vlookups" on imported data. These types of automation save time and money for the company. But sometimes the task that was automated grows and Excel is really no longer able to handle it.
At this point, the next logical step is a relational database such as Microsoft Access. At that point the power user will take it upon himself to learn Access and convert the Excel spreadsheet to Access. Even this many times will suffice and this Access automation will meet all the needs.
Sometimes these Access databases developed by a power user become popular and people from within and without the department really like it. These new users of this database will usually start asking for more functionality.
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. There is a learning barrier that is hard to break through on relational databases.
There are a few choices 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 someone like Affordable Automation to come in and give a recommendation. All three alternatives are definite possibilities. All should be considered.
If the decision in some shape or form to continue to use Access, the next ceiling is still quite far away. Access has a 2 gigabyte size limitation which is quite large for most tasks. I have developed databases with over a million records that still functioned quite well. Another limitation is the number of concurrent users. Access still functions quite nicely with several concurrent users. Access does quite well considering it is a file based system. Even the database is being used from a server shared network drive, all the processing happens on the desktop. So when a user makes a request, data is pulled over the network wire and the processing happens on the desktop. Since Access is a file based system and not a client server database, these limitations pop up.
But if one of the limitations are reached, there is still another easy alternative. That is to move the Access tables to Sql Server and link Access to Sql Server. Sql Server Express Edition, which is offered by Microsoft for free has a 4 giga-byte limitation. Even if this 4 giga-byte limit is not enough, Sql Server has other editions that are reasonable in pirce.
This Access - Sql Server combination in many ways acts a client-server solution. When a user makes the request from her desktop from Access, the processing now takes place on the server and only the results are brought over the network wire typically.
AA can come in at any point in this process and make a difference for your company.