More and more we want/need to be able to use an Access database and not be restricted to the confines of a local office LAN.  One potential solution can be to migrate the back-end (the tables) to Microsoft Azure and relink the front-end to the Azure SQL database, thus creating what is known as an Access hybrid database.

The beauty here is the back-end database is hosted in the cloud (in this case in the Microsoft cloud) and accessible to anyone that can run Access and whom has an Internet connection.  The drawback here is performance, although query tuning can greatly improve this aspect as well as using Azure SQL database views, and pass-through queries in Access.

The Overall Process

There are 2 key steps to this process:

1. Creating an initial File DSN
2. Linking the tables in Access

Creating an initial File DSN

The first thing we have to do is create a simple File DSN that Access will then use to connect to the Azure SQL database so we can create the linked tables for us.

The process is pretty straightforward.

1. In Windows 10, simply start typing odbc in the taskbar search field and select the appropriate version to suit your Office installation.  If you have a 32-bit installation of Office/Access then open the ODBC Data Sources (32-bit).  If on the other hand you have a 64-bit installation of Office/Access then open the ODBC Data Sources (64-bit).
   
2. Once the ODBC Data Source Administrator application opens, select the File DSN Tab and click on the Add… button
   
3. Scroll down the list of available drivers and select the ODBC Driver 17 for SQL Server and then click on the Next > button
   Now it is quite possible the ODBC Driver 17 for SQL Server will not be present on your system.  Do not use the plain SQL Server driver!  Instead, you must download and install it.  The page I found them on was Download ODBC Driver for SQL Server.  Install the appropriate driver based on your Windows OS bitness, not your Office/Access installation bitness.
4. At this point, you can browse in which folder you wish to create this DSN File (it’s basically a glorified text file!) and give it a name.  In my example, I’m naming it Azure_SQL_DSN.  Remember the location and file name as you will need these later in Access. Click on Next >
   
5. Click Finish
   
6. Enter a Description and the Azure SQL Server name and click Finish
   
7. Select With SQL Server authentication using a login ID and password entered by the user and then enter the Login ID (this is the Uid) and Password and then click Next >
   
8. Check the Change the default database to: checkbox, select the database you are wanting to create linked table to from the dropdown and click Next >
   
9. Check the Use strong encryption for data and click Finish
   
10. You should then see a ODBC Microsoft SQL Server Setup review dialog.  You can choose to Test Data Source … and finally click OK
    

The Azure_SQL_DSN.dsn file should now be created in the folder of your choosing, or by default in your Documents folder.  If you want, you can open it in Notepad or any other text editor to see how basic it is.  You can also edit it through the ODBC app at any time should any parameters ever need updating.

Linking the tables in Access

Now that the DSN file has been create, we are ready to create the linked tables in Access.  The process is actually remarkably simple!

1. Start Access and create/open the database in which you wish to create the linked tables.
2. Click on the External Data tab or the ribbon and then click on the ODBC Database command button
   
3. Select Link to the data source by creating a linked table and click OK
   
4. Go and find the DSN file created in the previous section, the Azure_SQL_DSN.dsn file, and click OK
   
5. Enter the Login ID (username/Uid) and Password and click OK
   
6. Select the tables for which you wish to create a linked table to.  I highly advise against using the Select All button as if you scroll, you will notice a great many system tables that should never normally be accessible to anyone.  Then click Ok
   
7. At this point, your linked tables should now appear in your list of Access tables
   

You are now ready to work with these tables.

Things to Note

DNS-Less Connection

Although we use a DSN file initially to create the linked tables, the end result no longer depends on the DSN file and thus can be distributed to your users without the DSN being created on their PCs.

Server Password

One thing you will notice though is that whenever you open your database and initially try to use any table/query/form you will be prompted to enter the password.

One option is to enter and save the password as part of the DSN File which will embed the password as part of the table connection string.  DO NOT do this as this make a major security hole as this information can easily be harvested as it is stored in the Access file as plain text!