1

I have two Microsoft Access 2013 Web Apps, hosted in SharePoint 2013 as part of Microsoft Office365. Both databases contain roughly the same tables and structure (with some minor tweaks).

We have two databases because our particular SharePoint server encountered a fluke error and irreparably corrupted the UI for the first Access Web App. In the end it was more cost-effective to just build a second Access Web App from scratch, rather then try to reverse the corruption in the original.

So now I have the duplicate created and functional, but I need to migrate data. The SQL backend for the original Web App is still fully functional, and the UI within Microsoft Access is also fully functional (it's the SharePoint side that got broken). So with Microsoft Access 2013 and SQL Management Studio available as my tools, what is the most efficient way to move data from one database to another? (Excel can connect and pull data, but can't insert records in the new database as far as I can tell, making it relatively useless. Let me know if I'm missing something here.)

Some of the problems I run into are, I don't have owner, DBA, or SysAdmin access to SQL - just data read and data write. I can't run any SQL queries that alter the table, such as renaming a column or Set Identity_Insert etc. (I was tinkering with the idea of setting the identity column, as one way of preserving existing relationships between records.) Additionally, Access 2013 doesn't provide the full ribbon for Web Apps, but instead only provides extremely limited functionality (screenshot below). Additionally, the query and macro buttons do not allow you to create queries or macros directly via code (SQL, VBA, etc.), but rather they force you to only use the query wizard GUI and macro GUI, and they do not allow you to connect to another database to pull data.

With these constraints, what is the best way to migrate existing data from the first database into the second?

Giffyguy
  • 20,378
  • 34
  • 97
  • 168
  • SSDT has data comparison: http://blogs.msdn.com/b/ssdt/archive/2013/06/24/announcing-sql-server-data-tools-june-2013.aspx – ta.speot.is Sep 03 '13 at 00:16

1 Answers1

1

If you create a new, empty Access 2013 database/application (not a web app), you can link to Sharepoint lists as though they are linked tables. I'd think this would be one way to move data from one Sharepoint instance to another, assuming both instances are still functioning well enough that you can connect to them this way. You should actually be able to write DAO code to transfer the data since a non-web Access Application doesn't have code disabled. Copy/Paste might work too although I wouldn't bet on it.

HK1
  • 11,941
  • 14
  • 64
  • 99