I'm not aware of a more recent version and I think it has changed since then as new functionality and flexibility has been added with new releases.

What don't your IT team give you access to SQL Server Management Studio with permissions to only the NBNData database, then you can query the data directly and can create your own views and extract tables as you wish. Or better still, also create a new empty database (e.g. NBNExtract) and then you can extract the data from the R6 tables into the new database on a regular basis and then run all of your queries on the new extracted data tables which will be a lot easier than navigating the complex R6 data model (and probably a lot easier than setting them all up in Azure).

Andy

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?

Andy