I may have some difficulty explaining this. We are creating a SharePoint site to house an inventory. We will need read access to everyone and edit privs for a few. Currently, I have the data in an Excel file in a quasi relational setup. (I share a primary key across tabs). I am trying to determine the best approach based on:

  1. End users are competent in Excel, but unfamiliar with Access and need to perform updates before a front end is built.
  2. We are using Office 2010 but upgrading to Office 2016. Access isn't a part of the suite. I do not know about the SharePoint 2010 being upgraded to Office 365 or whatever. Nor do I know whether infopath will continue to be supported.
  3. They want to use Infopath forms as the method of updating records.
  4. Users are worldwide with firewalls and VLANS. I am not sure if this is relevant to the initial design.
  5. I suppose I want to design this in SP2010 knowing eventually Sharepoint will be upgraded and don't want to reverse engineer it if possible.
  6. There aren't a lot of records up upwards of 300 fields. (I can split them into relational tables, but with Sharepoint lists, I don't know about different sources.
  7. Part of me says Excel would be more user-friendly, but Access more efficient although Microsoft seems to be moving away from Access

Access is more powerful by a long way but I've found it's confusing to new users, so if you intend on them capturing data through access without a front end I think you may struggle.

I would probably recommend getting people to capture into Excel and then automatically pull that data into the access database (either using the linked data import function or with a macro or similar... there are many ways to skin that cat)

Then just sync the access data with sharepoint lists. That way you'll have the data available in every format/ platform, which will offer you a lot of flexibility later on when migrating.

It's a long-way around for getting an excel sheet and a list to sync, but it's a heck of a lot easier for users to use IMO.

  • Thank you. First for understanding what I was driving at. I have more Access and VBA than Excel but not a lot of SharePoint. Your suggestion makes a great deal of sense, so off I go to research the best way to accomplish this. – Pat Arnold Jan 26 '18 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.