Access database is very user friendly tool to store relational data, devlop forms for data entry and data retrieval and formatted reports. However as the database grows and number of users accessing the database increases, the performance, accessibility and security of the data becomes important. For robust data platorm, consider migrating the access database to Azure SQL Server and continue using Access Database by linking Azure SQL Server database tables to the Access Database.
Follow below steps to migrate Access database to Azure SQL Server.
- Create a new SQL/use the existing SQL Server on Azure portal
- Create a new SQL database in the SQL Server (created/available in step 1)
- Follow steps provided in below link to migrate access database to the Azure SQL database
- Access to Azure SQL Database: Migration guide – Azure SQL Database | Microsoft Docs
- Download and install “Microsoft ODBC Driver 17 for SQL Server” on the users’ machine and create ODBC connection to access cloud data.
- Link the Azure SQL Server tables to the access database to continue using queries, forms and reports in access database.
- Make sure to add IP address of the client machine to the allowed IP addresses on the Azure SQL Server.
- Some of the access datatype will not work when the Azure SQL Server database tables are linked to Access Database. Login to Azure SQL Server Database and update datatype and relink the table in the Access Database.