It's okay to create tables in the R6 database, just give them clearly distinct names or, better still, put them in a different schema to they are clearly separate. You could also create SQL views instead of 'Make Table' queries which won't bloat the R6 database size. Neither should cause problems with upgrading R6.
SQL Server can't <b>link</b> to other databases, but you can reference another database by including the scheme and object name in the reference (e.g. NBNData.dbo.Taxon_Occurrence). So that would allow you to create SQL views, functions or stored procedures in one that references tables and objects in another database. But if you haven't got your own separate database with admin rights then that's not helpful.
At GiGL, and a few other LERCs that I know of, we have a separate database (called NBNExtract in our case) that contains a stored procedure to extract the database from R6 into one or more local tables (it could even be a single table but using two or three can be more space efficient). The stored procedure can be scheduled to run as frequently as required (or triggered manually) and then we have a set of SQL queries and views that reference the local tables. That stops us needing to add anything to the R6 database and makes it much easier to query the local tables because they have a much simpler data structure than R6. The local tables are also spatially enabled so that we can plot the data straight into GIS as points or polygons. Perhaps your IT would allow you this scenario - once the stored procedure, local tables and SQL views are created you wouldn't need admin rights, only permission to run queries against the tables or views.
As an alternative have you thought of using Python or R to access the R6 database directly instead of Access (or via the local tables as we'll be?
Out of interest, is the data copied into Azure (i.e. would it need to be regularly refreshed) or is it a live link once created? And presumably it would need to be amended in Azure if the R6 database structure is updated in the future?
Greenspace Information for Greater London (GiGL) CIC