Forum Discussion

Arun_Jagarlapudi's avatar
Arun_Jagarlapudi
Copper Contributor
Apr 10, 2023

MS Access Front End and Azure DB Backend

Owing to a short term requirement to use an MS Access application (Forms) while maintaining the data in Azure SQL, we are looking for the following information. Appreciate quick suggestions on the below:

 

1) Is it possible to maintain a front-end MS access application while maintaining the DB in an Azure SQL?

2) If MS Access Database can be pointed to Azure SQL, do we have any steps/instructions to follow?

3) Is there any way to change the connection setting in MS Access to point to another Database instead of MS Access inbuilt DB

 

Thanks in Advance!

 

  • LukeJMadden's avatar
    LukeJMadden
    Brass Contributor
    Apr 11, 2023

    Arun_Jagarlapudi Great question!

    Hopefully I can help.

    1. Yes, it is possible to maintain a front-end MS Access application while maintaining the DB in an Azure SQL.

    2. To point an MS Access database to an Azure SQL database, you will need to create a linked table in Access. To do this, you will need to have the appropriate permissions in the Azure SQL database. The steps to create a linked table in Access are as follows:

    • Open the Access database that you want to link to Azure SQL.
    • Click on the External Data tab and then click on the ODBC Database button.
    • In the Get External Data wizard, choose the option to link to the data source by creating a linked table.
    • Choose the option to connect to a data source by creating a new data source and click on the Next button.
    • Choose the driver that corresponds to your version of Azure SQL and click on the Next button.
    • Enter the server name and database name for your Azure SQL database, as well as your login credentials. You may also need to specify additional connection options depending on your specific configuration.
    • Click on the Test Connection button to ensure that the connection is working properly.
    • Once the connection is established, you will be presented with a list of tables that you can link to. Choose the tables that you want to link to and click on the Finish button.
    1. Yes, it is possible to change the connection setting in MS Access to point to another database instead of the MS Access inbuilt DB. To do this, you will need to modify the connection string for the linked table in Access. The steps to do this are as follows:
    • Open the Access database that contains the linked table.
    • Click on the Linked Table Manager button in the External Data tab.
    • Select the linked table that you want to modify and click on the Edit button.
    • In the Connection tab, modify the connection string to point to the new database. The format of the connection string will depend on the specific database that you are linking to.
    • Click on the Test Connection button to ensure that the new connection string is working properly.
    • Once you have verified that the connection is working, click on the OK button to save your changes.

    Note that if you have multiple linked tables in your Access database, you will need to repeat these steps for each table.

    I have not used MS Access for a while but this should work, let me know if you have any problems.

    Kind regrads,

    Luke Madden

Resources