One of the great strengths of Access is its ability to act as a front end to other data sources aside from standard Access database files. This gives you the ability to use the user interface resources built in to Access with the database engine strengths of a network database.
In recent times it has become possible to link Access to cloud-based database systems. This means that you can have a database application that can be accessed from anywhere with an internet connection and still keep your Access interface.
Although it’s possible work link to other systems, Microsoft’s Azure SQL Database (or whatever it is they’re calling it today) is a good bet. I have a number of databases that I have developed that use this combination and it works well.
There is however a common problem where the Access interface is unable to update the Azure SQL database. This is linked to certain field data types and can often be overcome by the addition of a timestamp field to the Azure SQL table. But this doesn’t fix the problem with boolean (Yes/No) fields.
When you convert an Access table to an Azure SQL table, it seems natural to use the ‘bit’ data type in place of the boolean in Access. Indeed, if you use a migration tool this will often be applied automatically. However this causes a problem with both Azure SQL and regular SQL Server.
The solution is to use the ‘int’ data type because this can hold the 0 and -1 values associated with the boolean data type in Access. SQL Azure gives you a web-based tool where you can manage the database including editing the fields in the table.
So changing from ‘bit’ to ‘int’ will fix this problem but there can be a problem when changing the data type of a field in the Azure SQL tables. You may get an error telling you that there is a dependency on the field that you wish to change and your change is rejected. Something like;
An error was encountered while applying the changes. An exception occurred while executing the Transact-SQL statement:
ALTER TABLE [TableName] ALTER COLUMN [FieldName] INT NULL
The object ‘DF__Table__Field__31B762FC’ is dependent on column ‘AnotherField’
Sadly the web interface doesn’t give you any interface to deal with field dependencies so you have to use an SQL statement to drop the dependency so that you can make the change.
Here’s the SQL statement you need to drop the dependency;
ALTER TABLE TableName DROP CONSTRAINT DF__Table__Field__31B762FC
After you’ve run that, you can make the change to your field data type.