Articles on all things data...

Migrating from Access to SharePoint

 May 23

by Andrew Richards

Can I migrate a database from Access to SharePoint?

I had someone contact me recently to discuss creating a database using Access.

One issue they faced was that there was nowhere obvious to put the data. 

Access can store the data in a number of places.

Most obviously, in an Access file - a .accdb file, which sits on a server, accessible by all staff who need to use the database. But in this instance, the company involved is planning to move away from shared network drives, so that's not an ideal solution.

For most scenarios, my preferred solution would be to use a SQL Azure back end - storing the data in a scalable, cost-effective, globally available, secure back end solves most problems.

But again, this company had IT policies which precluded this as an option.

So SharePoint became an option. Except that the IT department said to my contact "SharePoint is not compatible with Access". This despite Microsoft having various articles (here and here, for example) explaining how to do just this.

So, let me state this clearly

You can use SharePoint as a back end for an Access database.

There, I've said it.

But let me prove it.

Here's the button, in Access, to help you migrate your Access database to SharePoint:

Migrate your data from Access to SharePoint by using a button on the ribbon

And, in case that's not convincing enough, here's me, actually doing the migration of a sample (Northwind) database to SharePoint. 

I speeded up the clip in the middle - where the actual uploading of data happens - but beyond that, the clip isn't edited, tweaked or adjusted - the process just works. NOTE: The video clip has no sound - it just shows the steps to migrate your data.

How to migrate from Access to Sharepoint

Click to play

What problems are there in migrating from Access to SharePoint?

Just because the migration itself takes less than 2 minutes doesn't mean that the process ends there - any more than it would if you migrated from Access to SQL. There will still be things that need to be checked - such as:

  • Check that your lookup columns still perform as expected
  • Check that data in related tables still works
  • Check that your queries still return the data you expect
  • Check that the forms you use to add, edit and delete your data still operate as expected

Furthermore, if you've got VBA code, you'll want to pay particular attention to ensuring that the code which connects to the data still works as expected - that you don't get errors in opening ADO or DAO recordsets, or in retrieving calculations based on your queries and tables.

But again, this is the sort of standard checks that you'd have to do with any migration between two sytems.

So, if you're wondering about migrating from a Microsoft Access database to a SharePoint site, then the bottom line is that it's absolutely possible.

share this

Related Posts

Creating Excel Macros (part 1)

When should you use a database?

Excel’s data validation tool is not reliable…

Sign up now to be the first to hear about new articles!