Access Services and my data


This post is targeted at users who have a local set of data in a MS Excel file, MS Access database, SharePoint list, ODBC Database, CSV file, or any XML file.  All of these platforms are useful and have their merits.  You may want to extend this data to a large number of users, or you reached a network bottleneck that inhibits the performance of your source data, or experienced data corruption.    You need to move it to an 'Enterprise' backed service and are evaluating your options.

  • SharePoint On Premise: Access Services leverages an On Premise SQL Server set of instances/databases.
  • SharePoint Online: Azure Access Services leverages Azure SQL.

With Access Services you gain Enterprise scale and performance with integration points to SharePoint.  You can easily modify your source data in a familiar interface.   This post will demonstrate porting an excel file into Access Services.   I will then use an Access front-end (if ports 1433 are not blocked) with a back-end that is stored in an enterprise database via SharePoint Access Services.  In a future post I'll create a custom web application and consume the data via Entity Framework as well as extend through Business Connectivity Services to any site in your SharePoint farm or tenant.

 

The first step is to create the Access Services Add-In.  Use the out of the box controls to 'Add an App'.  We will search for the Access App.

Add Access Add-In

 

Provide a unique name for the Access app

Unique name for the Add-In

 

After some time the Add-In will be provisioned with its' isolated App Web

The Add-In is provisioned

 

Click on the new Access Add-In.  You can then open the Access Services' database in an Access web application database.

Launch the Add-In Access Db

 

We will create a table by importing an Excel file

Browse for an XLSX file

 

This will launch a wizard allowing you to modify column details from the first row of the Excel file.  Mark the first row as containing column headings.

ETL Process to identify column properties

 

Add a unique name for the table and process the import by clicking Finish

Import the data

 

You can tweak the table design or leave as-is.

Design mode

 

You can switch to Datasheet mode; view and/or edit data.  All data changes you effect are performed on the server and you are not required to save the file to persist changes.

Datasheet mode

 

Let's close the Access web application database.   Navigate to the SharePoint Add-In (if you are already there hit refresh) and you'll be presented with the table.  You can click on each row and view the detail pane with basic form capabilities.

View row in the Access Add-In

 

Switch to Datasheet mode and you can edit data just like you can with MS Access

Add-In switch to DataSheet mode

 

At any time you can customize the table(s) by opening the database with the 'Customize in Access' option

How to customize the data set

 

The Access Web Application (accdw) file will download.  Open the file to further customize your Access Services database

File downloads to modify table

 

Open the table to customize.  If you want to expand and customize the Access Web application take a look at this post https://support.office.com/en-us/article/Build-an-Access-database-to-share-on-the-Web-cca08e35-8e51-45ce-9269-8942b0deab26

navigate to the table

 

Edit the table

edit the table

 

Add a new column.  You can save the changes or close the design pane and be prompted to save changes.

Add new column

 

Add data to the new column by navigating to datasheet mode

Modify data in the new column

 

We successfully imported a table, modified the database schema, and updated data via the Access Add-In and Access Web database.  We can manage the database by setting the database connections (firewall rules and permissions).  From the manage connections screen we can also review the database connection properties.

File > Info

 

Set the firewall rules to enable a connection to the Access Services database from Any location

Set firewall rules to allow access

 

Enable Read-Write connection.  This will allow us to read/write from an Access database, SQL Server IDE, Web Application, or BCS Services

Enable a Read-Write connection

 

After we enable the connection for Read or Read-Write we can now view the Connection Information

View connection info

 

You can view this connection information anytime by launching the ACCDW.  Take note of the username, password, server name, and database name.  The user account will not be able to enumerate other databases on the server.  All connections to the database must specify the database name as the default database

Database connection info

 

We will create a DNS file using notepad or any other text editor.  Take note of the specific variable names.

  • [ODBC]
  • DRIVER=SQL Server Native Client 11.0
  • UID=[your username]
  • DATABASE=[your database name]
  • WSID=[your computer name]
  • APP=Microsoft® Windows® Operating System
  • SERVER=[your server name]

Create DSN with connection properties

 

Now that we have the connection information and a DSN file we can create a link table in a new Access database.  Let's link to a data source by choosing an ODBC data source.

Open external data and link to data source

 

Navigate to the DSN file

Navigate to DSN file

 

You'll be prompted for a password.  Use your password from the connection information

 

Once you click Ok you'll be presented with a list of tables from your Access Services database.  Select the table you've created.  You can leave the Save password unchecked and be prompted for the password next time you open the access db.  In this sample I'll check the Save password option.

Select table and save password

 

 

The Linked table is created in the access database.  I hovered over the table to display the database connection properties.

demonstrates connection properties

 

Open the table in Datasheet mode to view the data in the database and change it.

Edit data

 

 

In this screenshot I'll demonstrate the same data rendering in the Access web application db and the Access db through the linked table.  You can edit the data in either one, hit refresh in the other db, and view the modified data.

Front end and back end with same data

 

In the next two blogs we will use the same Access Services database to expose the data via two different options

  1. A custom web application via Entity Framework.
  2. Business Connectivity Services via external content types and an external list
Comments (0)

Skip to main content