This MS Access thing has really been bugging me (See my last post on Access). I currently build lots of solutions that help implement serious business process improvements using InfoPath, SharePoint Designer, and Workflows. My colleague and I can carefully listen to the requirements of business users and then generate very effective solutions quickly. In many cases we have the solutions up and running in less than a week.
These business processes range from HR (on-boarding, off-boarding, benefits) to Finance (obtaining budgetary numbers from various departments on a regular basis and aggregating them) to supporting Scientists (Ensuring that scientific equipment is ordered, purchased and delivered in a timely manner.)
So, why has this MS Access thing been bugging me? If there are better tools out there and the world is moving toward them, I want to know and I want to get onboard. In addition to this, I work with several people who are part of the business (i.e non-techies) and they love MS-Access. They have asked me whether their Desktop Access Databases can be published to SharePoint 2013. So I did some investigation.
I distilled my research, to the following three questions:
- It seems that Access 2013 has special templates that start out as "publishable" to SharePoint 2013. I have a number of clients that have Access Databases that already exist and they would like to publish them to SharePoint 2013. Must we start from scratch using one of the Access 2013 SharePoint templates? Is there a way to publish an existing Access Database that was not created using the Web template? It feels to me like you have to decide which fork in the road you are going to follow (Web based App, or Desktop App) at the time you first create an Access 2013 DB? Is that correct?
- I often build some complex custom SharePoint Designer Workflows for clients. I usually use InfoPath as the vehicle for interacting with a user in a fairly elegant manner. If I wanted to use Access 2013, is there a way to leverage SharePoint Designer workflows and have them dovetail with Access 2013 tables? A bonus question, if I don't love the Access 2013 forms, is there a way to use InfoPath 2013 forms with an Access 2013 SharePoint based Database?
- I understand that Access 2013 DB, when published to SharePoint 2013 saves its tables to SQL. Using various menu options I was able to find connection information to the tables. My question is on Office 365 can I use SharePoint designer to create a view of the SQL table inside SharePoint online. If so, can someone point me to an example where this is illustrated. Finally, is it possible to create an external content type on Office 365, so that I could interact with the SQL table as if it were a list?
I finally got my answers from Greg Lindhurts [MSFT] on the dev.office.com support forum. I thought these answers might be interesting to others:
- Web and desktop databases are based on very different technologies, with very different programming models, and there is not a 1-1 mapping between them. Hence, why there is a fork in the road when you create a new application, and unfortunately, it is not possible for us to automatically migrate desktop databases to the web. What you can do is create a new web database, and import all the schema, relationships, and data (at the bottom of the "Add Table" screen). From there, we will automatically create a new application frame and forms for all the tables, as a starting point. We have done this with Northwind in demos and it works pretty well, as a starting point. VBA will not move forward and will need to be rewritten in terms of UI and Data macros. Reports will need to remain in the Access client.
- At this time, Access does not integrate with workflows or InfoPath forms. Access can read SharePoint lists, so one could use an InfoPath form to fill in information, and then read the information from Access.
- You can use any tool you like that can talk over ODBC, including SharePoint Designer, to connect directly to the database. I don't have any examples off the top of my head, but I'm sure some will appear over time. I do not know content types well enough in SharePoint to know advise you on your last question.