Best option for web access/multi user Access database?

if we link the Access data with Azure through SQL, then we no longer need Access, since it's been replaced with SQL.

Is it possible to simply link Access and it's relational data to Azure? 

You still using Access as the front end. So, Access is a great front end tool, and even when the back end is SQL server. So, in fact what i am saying, suggesting here is that you can continue to use Access, but the back end is sql server.

You can't really "replace" Access with SQL server because SQL server does not have any user interface parts.

Remember, when we say Access, we require the correct context. 

Access has two parts:

The FE end builder parts. (forms, code, reports).

And then it has the database engine part (used to be called JET, but now is called ACE).

So, just like some older VB6 programs, or even accounting programs? Well, they were written in .net or say VB6, but they used Access as the data store. (the ACE data engine). So, the same goes for say a web site. You "can"  use the Access database part - but you NOT using the Access development part.  So, the database engine and technology used in Access is "ACE" or the local built in database engine.

So, you can dump using the Access data engine, and adopt SQL server. But you STILL using Access as the front end. So yes, you can use Access + ACE (that is the default - what you been using).

but you can use Access + SQL server.  The key concept here is that ACE does NOT work over a VPN or a internet  connection. So, you can use/adopt SQL server. But SQL server is ONLY the database engine part. You still have to build a front end to sql server.

That front end can be:

Vb.net, or c# from .net

VB6, or even Foxpro, or some other system.

Or the front end can be Access!

So most of the time when we say "access", the context does not matter. But you might use vb.net + Access data engine. In this case, you not using nor developing any of the forms or UI in Access - but you using the ACE data engine.

>It seems like there are an infinite ways of doing this...

Yes, there are as many approaches here as their are flavors of Ice cream!

You have to nail down some requirements.

So, for example, can/will it possible (or practical) to have workers in the field have internet? This is a big question, and the answer will be a compass as to what your practical options are.

If it is not practical to have in the field internet? Then using remote desktop, Azure or any kind of web or remote technology that requires the internet is not going to work, right? Ok, then we wipe all those options off the board. So, while it "seems" there is a zillion ways to do this, that actually not really true.

If you MUST have off-line ability? Well, one really amazing cool option with Access? Using office 365 on-line, and use the SharePoint tables option.  This is a cool option since it automatic allows off-line (no internet) and then when you have internet, it syncs with a central database. This option is absolute fantastic, and you get to do all development in Access. However, the ONE big issue here is that some HUGE restrictions exist. For one, the tables max rows has to be small. (below 5,000 rows). So, if this solution fits for you, then it is Mr. Perfect solution.

However, if your application and data sets (tables) are larger then 5,000 rows, then this solution likely not to work for you.

So if you must have off-line local mode? Then you can have each user in the field do data entry. But when you come back to the office, then they could up-load the data to a common database. (sql server likely the best choice here).  The problem with this solution is you have to write a sync routine of some type. (or if you really don't want to do this? - then you run SQL server express on each laptop in the field). When you get by you use the  sync services that sql server provides.  This however, will require to to be running a non free edition of sql server for the "main" database. Each laptop can run the free sql server, but to sync (without you having to write code, the main database can't be sql express - it has to be the next version up).

In general, a remote desktop solution is near always the best solution for Access remote use - but it does require a live internet connection. 

If you need off-line and your database is relative small and not complex? Then the office 365 and SharePoint table is the winner. But ONLY if your data fits within the limited sizes (5,000 rows max for the tables in your database).

>Is it possible to work offline within Azure, and have the changes auto-update once the user connects to the internet?

Only the SharePoint list option and Access is automatic (without you having to write code).

And you can do the same ***if*** you run sql server (and Access) on each laptop - then this can be setup to also sync for you.

And, you could have 100% access without SharePoint or sql server, and write your own sync routines - but that is a bit messy.

So, the only zero you writing any code? That would be using SharePoint tables - and office 365 still supports this option - and it means you don't really have to change your application - but this only work for light weight applications without a lot of data. 

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada