Microsoft Access is a great tool for small businesses, allowing you to jump into a database without investing a lot of time and energy. I'm a big fan of using it this way, even though I've got lot of experience with larger-scale databases, like SQL Server. Companies that are too small to have a full-time IT staff benefit greatly from having a database tool that they can tweak without extensive training, and I've developed many such tools over the years using Microsoft Access.
There are some down-sides to Access, though. Microsoft Access files are more prone to corruption than other database tools. As your database grows, you can start to experience performance issues as well, especially if you've got lots of concurrent users.
The good news is that there is a relatively easy upgrade path. One of the best features of Microsoft Access is that it allows you to create forms, queries and reports based on data outside of Access. Got some data in Excel sheet or an Oracle database, for example? No problem. Access can link to those.
That means that you can develop a full application in Microsoft Access, and later port the data to a more advanced database product, like SQL Server, without losing the investment you've made in developing forms, queries and reports. In a typical example, you might have a dozen tables in your Microsoft Access database, plus hundreds of queries, forms and reports. When you're ready, you'll move your tables to SQL Server, leaving the queries, forms and reports in Access. Once you link the two together, you're application will continue to function in its familiar way, making for an easy transition.
The process is, theoretically, quite simple:
Create new tables in your SQL Server database, with the same column names and data types as your original Access tables
In your Access database, link to those new tables
Use queries to move the data from the original Access tables to the new SQL tables
Delete the Access tables (after backing up, just in case!)
Rename the SQL tables to match the names of the original Access tables
In practice, it tends to be a little more complicated. You'll may have data integrity issues with your tables that require you to load up the new tables in a particular sequence. You may have a feature called "Name Autocorrect" turned on in Access which can sometimes wreak havoc when you rename tables. You may decided that this is a good time to revisit and correct any database design issues, and if you do that, the upgrade process will be more intensive.
Still, it's not a hard process for anyone with a reasonable amount of Access experience.
My recommendation? Unless you've got a very experienced Access developer handy, this might be a good time to call in a pro. You may have reached the point where your in-house database skills need to be supplemented by an outsider who has done these types of transitions before.
And that, of course, is where I come in! I do these types of one-off projects all the time to help clients get over a hurdle like database conversion. The best part, for me, is that I often develop a lasting relationship with these clients, helping them out as needed over the coming months or years.
Ready to talk your project over? Set up a call with me, at no charge - your initial consultation is always free! You can tell me about your plans and I'll make recommendations on how to proceed. If you decide to move forward with a proposal, I'll develop a written project plan, including a "not to exceed" cost. No surprises!
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at firstname.lastname@example.org.
© 2023 North Port Solutions, LLC. All right reserved.