Maybe you’ve been using Access Web Apps for a while now and have got to the point as I have where i want to do something more with the information. I want to be able to report on it, export it, play with etc.
Immediately there’s a bit of an issue, where are all my export options, my reporting options? They aren’t there.
Access Web App’s has been built, unfortunately, with no native reporting, which means I need another method for getting to my data.
Where is my data
Firstly, we need to consider where the data is actually stored. When you add your Access App in SharePoint, and new, dedicated Azure SQL Database is created for it.
If you open your Web App in access on your desktop and go to the ‘info’ tab, you’ll be able to see where the database is stored.
Accessing the data
In the ‘info’ tab, you’ll also notice that there are 2 other options
Report on my Data
This option will create local access database which links to the table in the web app’s Azure database.
This will also you to use the full power of the Access Reporting services on your data.
This option will allow you to manage how your web app’s data can be accessed. There are a number of options here, so you need to carefully consider what is best for your app and the integrity of the data inside it.
Accessing my Data from outside of Access
I’m only looking to report on my data and don’t need manipulate it in anyway, so I’ve opened connected and enabled the ‘Enable read-only connection’.
Once this is enabled, the option ‘view read-only connection information becomes available to be viewed, which will give you the Server name, database name and the credentials needed to connect other software to this database.
Accessing your Data from SQL Management Studio
Now you have enabled the connection, you can access the data from lots of differnet places, including SSMS (SQL Server Management Studio)
To this you need to specify the server and credentials as you would normally, but as this is server we don’t own or have any control over, you also need to specify the exact database you wish to work with from the ‘Connection Properties’ tab.
Once you are connected, you have access the Database as you would normally and can work with it.
Note: As the whole web app is stored in the SQL database you need to be very careful not to mess with any of the tables that don’t look like your data.
Accessing your data from Excel
Similarly, you can use Excel to access this information using the ‘From Data Connection Wizard’