Alternative to Microsoft Access: SQL Server + Excel

In this article we’re going to look at why you might consider an alternative to a Microsoft Access database for your organization, or specific project. As we’ll see, Access has some limitations that can become a problem eventually. A simple solution is to use a combination of SQL Server and Excel, with the SQL Spreads Add-In for Excel providing a very useful link between the two.

Why do you need an alternative to Microsoft Access?

Microsoft Access (Access) has been around for a long time. The chances are that you will have used it at some point in your working life, and there are still many users out there today. Access is often the starting point for individuals or small businesses that need to create relatively simple databases and applications.

Access is often used for tasks such as managing contacts, inventory, projects, and other structured data, and for these small-scale applications it works well. However, Access does have some limitations that users should be aware of, especially when considering its use for more complex or demanding tasks.

Here are some of the main limitations of MS Access:

• Scalability and Performance:
  • As database size and complexity increase, performance can degrade significantly.
  • Not well-suited for environments with a high number of concurrent users.
  • Susceptible to corruption if not handled carefully.
• Security and Data Integrity:
  • May not provide the same level of security as more advanced database systems.
  • Prone to data integrity issues, especially in multi-user scenarios.
• SQL Compatibility and Data Types:
  • SQL implementation might not be fully compatible with other popular databases.
  • Supports a limited set of data types compared to more advanced databases.
• Development and Collaboration:
  • Typically desktop-based, lacks native support for web-based deployment.
  • Might lack some advanced development features available in more powerful development environments.
  • Collaborative features like version control and seamless collaboration among developers are more challenging to achieve.

In the following sections we’re going to look at an alternative to Microsoft Access: a solution that uses SQL Server as the back end and Excel as the front end. This includes the use of the SQL Spreads add-in for Excel.

The Solution: SQL Server + Excel + SQL Spreads

Some organizations overcome the limitations of Access by adopting a hybrid approach where Access is used as the ‘front-end’ of a program (such as an inventory app) with other products being used for the back-end database, such as Microsoft SQL Server. This alternative to using a Microsoft Access database overcomes many of the limitations we mentioned in the previous section by using a robust and highly scalable and secure database application such as SQL Server.

However, if you want to move away from Access entirely, whilst using SQL Server as the backend database, what is the best option for the front-end application?

In an earlier article, we looked at how you can use everyone’s favorite office application, Excel, together with the SQL Spreads Add-In as a front end for SQL Server.

Here is how the proposed solution compares to Access:

You can see that Excel performs the job of the front end, whilst SQL Server provides the back end database functionality. SQL Spreads is an Excel Add-In that acts as a two-way connector between Excel and SQL Server, to let your end-users view and also update SQL Server data. Download the 14-day trial for SQL Spreads to get started.

SQL Spreads also has a number of features that enhance Excel and make it a good front end for working with SQL databases.

With the SQL Spreads Add-In installed, users can perform the following from within Excel:

• Connect to SQL Server and view data in tables and views
• Update, Insert and Delete data in SQL (based on their permissions)

Additionally, those with design permissions can control how users in their organization interact with the data by using features such as:

• Tree filters and lookup columns to help users navigate their data
• Post-processing to validate changes before they are committed to the database or perform other updates
• Auditing to show which users made changes and when
• Data pivoting to show data in a summarized way

Finally, SQL Spreads also includes some tools to perform some basic SQL Server administration tasks from with the Excel interface:

• Create tables in SQL Server using data from Excel
• Modify table columns (change, add, delete) rename tables in SQL Server
• Copy tables in SQL Server from one database to another.

Let’s take a quick look now at what is involved in creating an alternative to a Microsoft Access database.

Migrating an Access database to SQL Server

Microsoft provides a guide on how to successfully migrate an Access database to SQL Server using the Microsoft SQL Server Migration Assistant (SSMA).

The process involves the following main steps:

Step 1: Preparation of Access data and target SQL Server database

There are two main things to prepare for the migration; the first is the data in the Access database, and the second is the target SQL Server database.

There are a few things you can address up front that can help streamline the migration process before you run SSMA:

• Add table indexes and primary keys: make sure each Access table has an index and a primary key.
• Check primary/foreign key relationships: make sure these relationships are based on fields with consistent data types and sizes.
• Remove the Attachment column: SSMA doesn’t migrate tables that contain the Attachment column.

When it comes to the target SQL database, the main decision is whether you are going to use Azure SQL Server or an on-premise instance of SQL Server. For the Azure option, you also need to choose between a single database, a managed instance of SQL Server, or an Azure virtual machine in which you can run SQL Server. You can get more information in this article on Azure SQL Server.

Step 2: Running the SQL Server Migration Assistant (SSMA)

SSMA mainly migrates tables and select queries with no parameters. Forms, reports, macros, and VBA modules are not converted.

To migrate a database using SSMA, first download and install the software. After installing SSMA, open it on your desktop, preferably from the computer with the Access database file. Follow the instructions in SSMA to provide basic information such as the SQL Server location, the Access database and objects to migrate, connection information, and whether you want to create linked tables.

Step 3: Converting Access Objects to SQL Server Objects

SSMA converts Access objects to SQL Server objects, but it doesn’t copy the objects right away. SSMA provides a list of the objects to migrate so you can decide whether you want to move them to SQL Server database; these include tables/columns, primary and foreign keys, indexes, check constraints and select queries.

Converting database objects takes the object definitions from the Access metadata, and then converts them into equivalent Transact-SQL (T-SQL) syntax. Take a look at this article for more information on migrating objects to SQL Server.

Setting up SQL Spreads Excel Add-In as the front end

Now that you have migrated your data from Access to SQL Server, you can start setting up the SQL Spreads Add-In front end for Excel.

Step 1: Download SQL Spreads Excel Add-In

Download and install the SQL Spreads Excel Add-in. Once the SQL Spreads Add-In is installed, you’ll see the SQL Spreads tab in the Excel ribbon menu:

Step 2: Connect to your SQL Server database

Initially when opening up Excel after the SQL Spreads installation, a dialog will pop up prompting you to connect to Microsoft SQL Server. Here you can enter the details of your newly migrated database.

Step 3: Choose and setup your SQL Server table

Once you have successfully connected to SQL Server, then a list of your databases and corresponding tables will be displayed in the SQL Spreads Designer. The SQL Spreads Designer pane is where you can choose which SQL Server table to load into Excel. Then you can set up column properties, filters, sort options and other settings.

Step 4: Edit your SQL Server data

Edit the data in your SQL Server table and click on Save to Database button. Now, your updates will be saved back to your new SQL Server database. In the example below, a solution has been created so that users can update budget and actual sales figures for different regions and departments

You can build a front end solution that includes multiple sheets, each one connecting to a different table or view in your database.

Here is another example of a Financial Forecasting Model in SQL Spreads using data from SQL Server as well as several features in Excel. Here is a brief summary of the main features of the demo:

• Connection to a forecasting table in SQL Server
• Tree filters to select forecast period, company and region
• Editing of Budget, Actual and Forecast values
• Calculation of variance
• Adding of comments (if relevant)
• Change tracking (date and person last modified)

Conclusion

If you or your organization uses a Microsoft Access database solution for one of your in-house apps, you probably enjoy it’s flexibility and ease of use. Access does, however, have some limitations, that we looked at in this article. If you are therefore looking for an alternative to a Microsoft Access database, and you want a solution that is also flexible and easy to use, then our proposed solution of Excel + SQL Server + SQL Spreads is a good option.

The process of migrating data from an Access database to a SQL Server database is relatively straightforward with the SQL Server Migration Assistant (SSMA) from Microsoft. The other part of the solution involves using Excel with the SQL Spreads Add-In as the front-end application. We all know that Excel is ubiquitous in the workplace, and with the additional user-friendly and security related features added by SQL Spreads, the solution becomes a compelling alternative to a Microsoft Access database.

Download a trial version of SQL Spreads and see how easy it is to connect to SQL Server and create user-friendly and secure front end applications for your data.

Article by

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.