Did you know that you don't have to completely rewrite your Access apps in order to make them more accessible? Here's how you can use Office 365 APIs, Access Services and SharePoint to turn them into apps that make everyone's life a little easier.

As a quick note, Access apps may not be what you'd pick for development, considering the user interfaces are often primitive and the apps don't usually have good security (they may not validate data quality or include backup features). And because the Access runtime is bundled into the app, they can end up on desktops and laptops around your company long after the original creator has moved on, yet they aren't compatible with tablets or phones.

Thankfully, the new Access apps introduced in Access 2013 avoid many of those problems, because what you're creating here are actually web apps; the UI is HTML and JavaScript, the data tables, views, macros and queries are stored in SQL Server (using SQL triggers for data macros), and the app itself is hosted in SharePoint. You get central access and version control; SharePoint provides the authentication, authorization and security.

[caption id="attachment_53458" align="aligncenter" width="580"] Architecture of SharePoint 2013 web apps (credit: Microsoft)[/caption]

You can distribute these through your corporate catalog or through the Office Store, making it easier to manage who can use them; you can make them available to partners and customers, or you can sell them.

You get the security of running on SharePoint, the scalability of SQL Server (you can use a separate SQL Server server if you need to scale up) and the user interface looks more modern, yet building the app isn't any more complicated than building an Access application – all the SharePoint and SQL coding is done for you. And because you're getting a web app, it's not limited to running on Windows with the Access runtime; it can run inside Office Web apps including SharePoint Online (on Office 365 or on premise), or on phones and tablets.

The mileage track app in the Office Store was built in Access. And if you don't see a template that you like in Access 2013, you can start with one from the Office Store. First, run it and choose Customize in Access (located in the top right corner), and then tweak it as necessary.

Access 2013 apps can also be used as a way of bringing other Access applications under control – all the way back to Access 2003. Start by tracking down Access databases and .MDF files that you want to migrate or extend. Volume Licensing customers can use Microsoft Discovery and Risk Assessment Server to find them; this crawls network shares and SharePoint sites and collates relevant information (like the data connections used in Access and Excel files, and who they were last saved by).

It will also warn you about errors inside files, like broken links and incorrect formulae. There's a handy guide to using Excel to view and filter the data from DRA Server here.

Once you have the app you're working with, decide whether you want to make it available to users through Access Services in SharePoint as a web app, or if you want to keep the Access app itself for desktop users. For example, it might have VBA macros that only a few users need, and you don't want to recreate for all users.

To convert the original Access app, you'll want to create a new Access web app. You can do this directly in Access 2013 or by using the “add an app” option from the settings menu in SharePoint Online (choose Access, then click to customize it –  you don't have to fill in the Web Location in Access). Either way, you'll end up at the Add Tables page. Under “Create a table from an existing data source” choose Access and browse to the .MDF file. This starts a wizard that lets you pick the tables you want to import.

[caption id="attachment_53459" align="aligncenter" width="655"] Import the tables from your original Access app to the new Access web app[/caption]

Views replace the forms you used in Access runtime applications, and when you create a new table (or import one from an existing app) you automatically get list detail and datasheet views, both of which include an Action Bar with buttons for adding, editing, saving and deleting records. The new table also shows up as a link in the tile pane – a list of all the tables that make up your app, that you use for navigation. You can add summary views or an initially blank view that can be customized with the grid-like view designer. Use AutoComplete to speed up data entry and Related Items to show records from related tables.

Custom macros let you create user interface navigation options like switching to another view or applying a filter; they can stay in the Action Bar or be attached to UI objects like buttons and combo boxes. Data macros run when records are added, updated or deleted. For more details about customizing Access web apps, see the steps for creating an issue-tracking app here.

The List and Datasheet views lay out all the fields from your table, and Access makes some pretty good guesses about what to include, but you can tweak as necessary. You can edit the new Access web app as much or as little as you want (remember to change the name of the app from My New App). When you save it, it becomes available on SharePoint in the app catalog and is ready for mobile users.

If that's your goal, you're done.

[caption id="attachment_53460" align="aligncenter" width="645"] Our original Access app converted to an Access web app running on Office 365[/caption]

But, if you want to take it a step further, you can convert the tables in the existing Access app to linked tables – pointing at the same SQL Server tables that your new web app uses. That way, users can view and edit data in either of them (and use the VBA features in the original Access app), and SQL Server keeps the changes in sync. You can even keep the new database file in SharePoint to manage who has access to it.

Open the original app in Access and delete all the tables (Access will warn you about relationships between tables; click through the warnings).

Now open the new web app in Access and choose File > Info > Manage Connections. First choose Enable Read-Write Connection, then choose Manage Connections again and select View Read-Write Connection Information. Copy the four fields, which give you the server and database identifiers, and the username and password to connect to it.

[caption id="attachment_53461" align="aligncenter" width="573"] You'll need to copy the access information from the new web app to the original app to link the tables[/caption]

In the original app, click the External Connections tab on the Access ribbon and choose ODBC database. In the wizard that opens, choose to link the tables rather than import them. On the next page of the wizard, create a new connection; pick the Native SQL Server client and click the Advanced button and paste the server, database, username and password from the web app. Work through the wizard, picking the tables you want. The table names are often linked with the Access_prefix, so you may need to clean these up. Once you've done this, your Access app will work just the way it used to, but with the Access 2013 runtime – and the same data will be in the web app.

Access Services is available in every Office 365 tenant plan, and as of spring 2014 it's also covered by the same SLA as the rest of Office 365. If you want to do this with your own SharePoint Server, you need to run SharePoint Server Enterprise 2013 (with the March 2013 public update), and SQL Server 2012 (you don't need to put the whole SharePoint farm on SQL Server 2012, just the server you use for Access Services). There's a good guide to configuring Access Services here.

If you're dealing with a Lotus Notes migration, Access Services might also come in handy. Email and calendars from Notes go into Exchange, and groups can become public folders or mailing lists, but you'll also have Notes applications and templates to migrate. Some of them will work as SharePoint lists and libraries, while others will do best as workflows, but you also have the option of recreating them as Access web apps.

As we've seen, Access Services lets you migrate your Access apps to the web, where everyone can use them. But if you still want to keep Access applications, you can use SharePoint to manage the way they're distributed. You can also make the same database available to mobile users while adding more features to the Access app, like embedding JavaScript to pull information from web services and APIS and displaying it in an IFRAME. You could even use that feature to write to SharePoint lists from inside an Access app, and trigger workflows and send messages.

Basically, you can put all your complex workflow and business logic on SharePoint, where it can be used in many different ways, instead of duplicating it in Access desktop apps.

[caption id="attachment_53462" align="aligncenter" width="614"] Office 365 APIs offer options like using the Bing API to include a map in your Access app[/caption]