SOLVED

MS Access online

%3CLINGO-SUB%20id%3D%22lingo-sub-1325013%22%20slang%3D%22en-US%22%3EMS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325013%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20how%20does%20online%20version%20of%20access%20works%3F%20For%20now%20I%20am%20only%20working%20in%20the%20desktop%20application%2C%20but%20I%20see%20a%20challenge%20when%20I%20need%20to%20share%20the%20data%20with%20more%20people%20in%20the%20organization.%3C%2FP%3E%3CP%3EI%20am%20also%20considering%20collecting%20some%20data%20from%20my%20database%20into%20a%20PowerBI%20Report%2C%20is%20that%20working%20okay%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1325013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325751%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F160048%22%20target%3D%22_blank%22%3E%40Cleo%20Otterbech%3C%2FA%3E%26nbsp%3BThere%20is%20currently%20no%20%22online%22%20version%20of%20MS%20Access.%20Three%20prior%20attempts%20to%20implement%20that%20have%20proven%20inadequate%20and%20were%20deprecated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20share%20the%20data%20with%20other%20users%20in%20your%20organization%2C%20though%2C%20in%20a%20few%20different%20ways.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20start%20by%20splitting%20the%20data%20from%20the%20interface%20objects%2C%20just%20like%20you%20would%20with%20an%20online%20database.%3C%2FP%3E%3CUL%3E%3CLI%3EPut%20the%20tables%20(the%20objects%20containing%20your%20data)%20in%20one%20accdb%2C%20which%20is%20usually%20referred%20to%20as%20a%20Back%20End.%3C%2FLI%3E%3CLI%3EPut%20the%20interface%20and%20logic%20objects%20(forms%2C%20reports%2C%20queries%20and%20VBA)%20in%20another%2C%20which%20is%20usually%20referred%20to%20as%20a%20Front%20End.%3C%2FLI%3E%3CLI%3ELink%20the%20Front%20End%20to%20the%20tables%20in%20the%20Back%20End.%3C%2FLI%3E%3CLI%3EPlace%20the%20Back%20End%20accdb%20on%20a%20network%20location%20where%20all%20of%20your%20users%20can%20connect%20to%20it%2C%20i.e.%20in%20a%20shared%20folder.%3C%2FLI%3E%3CLI%3ECopy%20the%20Front%20End%20accdb%20to%20the%20computers%20of%20each%20user.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAnother%20option%2C%20if%20you%20must%20share%20the%20relational%20database%20application%20with%20users%20outside%20your%20network%2C%20is%20to%20place%20the%20tables%20in%20a%20remote%20SQL%20Server%20or%20SQL%20Azure%20database%2C%20i.e.%20a%20%22cloud%22%20location%20to%20which%20all%20users%20can%20link.%20If%20you%20go%20this%20route%2C%20of%20course%2C%20the%20Front%20End%20accdb%20links%20to%20that%20cloud%20database%20and%20each%20user%20gets%20their%20own%2C%20individual%20copy%20of%20that%20Front%20End.%3C%2FP%3E%3CP%3EThere%20are%20other%20options%2C%20but%20we'd%20need%20more%20information%20on%20your%20business%20environment%20to%20know%20if%20they%20would%20be%20appropriate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1327736%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1327736%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%2C%20today%20we%20are%20sharing%20via%20our%20internal%20network%2Ffolders%20and%20only%20internal%20users%2C%20not%20externals.%20The%20way%20it%20is%20today%20I%20would%20be%20happy%20to%20be%20able%20to%20share%20at%20list%20the%20reports%2C%20I%20was%20thinking%20Power%20BI%20could%20be%20a%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328666%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F160048%22%20target%3D%22_blank%22%3E%40Cleo%20Otterbech%3C%2FA%3E%26nbsp%3BFor%20reporting%2C%20Power%20BI%20is%20a%20great%20choice%2C%20IMO.%20Of%20course%2C%20that%20implies%20you%20have%20a%20site%20where%20the%20PBI%20files%20can%20be%20published%2C%20e.g.%20an%20O365%20site.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1933794%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1933794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3BHi%20again%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20build%20my%20Access%20database%20with%20tables%20and%20linked%20tables%20from%20SharePoint.%20An%20I%20am%20also%20using%20this%20data%20to%20build%20a%20PowerBI%20Report%20to%20share%20the%20data%20with%20the%20organization.%3CBR%20%2F%3EWe%20are%20sharing%20the%20application%20on%20a%20internal%20shared%20location%2C%20but%20what%20I%20see%20now%20is%20that%20when%20one%20open%20the%20database%20it%20is%20locked%20by%20the%20user%20even%20know%20none%20table%20is%20being%20used.%20Does%20this%20has%20something%20to%20do%20with%20the%20linked%20tables%20from%20SharePoint%20sites%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934341%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F160048%22%20target%3D%22_blank%22%3E%40Cleo%20Otterbech%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20quite%20sure%20how%20you%20are%20sharing%20the%20relational%20database%20application%2C%20but%20if%20you%20mean%20that%20you%20have%20ONE%20copy%20of%20the%20Access%20accdb%2C%20and%20everyone%20is%20trying%20to%20use%20that%20same%20accdb%2C%20then%20that%20is%20the%20problem.%3C%2FP%3E%3CP%3EEach%20user%20MUST%20have%20their%20own%20copy%20of%20the%20Access%20accdb%2C%20all%20of%20which%20are%20linked%20to%20the%20tables%2C%20or%20in%20this%20case%2C%20the%20SharePoint%20lists.%3C%2FP%3E%3CP%3EEach%20user's%20accdb%2C%20which%20we%20call%20a%20%22Front%20End%22%20or%20FE%2C%20should%20be%20on%20their%20own%20computer%2C%20not%20in%20a%20shared%20location.%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20%22split%20database%22%20protocol%20is%20the%20only%20safe%20way%20to%20share%20a%20relational%20database%20application%20among%20two%20or%20more%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20won't%20be%20related%20to%20using%20SharePoint%20lists%2C%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, how does online version of access works? For now I am only working in the desktop application, but I see a challenge when I need to share the data with more people in the organization.

I am also considering collecting some data from my database into a PowerBI Report, is that working okay?

5 Replies
best response confirmed by Cleo Otterbech (Occasional Contributor)
Solution

@Cleo Otterbech There is currently no "online" version of MS Access. Three prior attempts to implement that have proven inadequate and were deprecated.

 

You can share the data with other users in your organization, though, in a few different ways.

 

You will need to start by splitting the data from the interface objects, just like you would with an online database.

  • Put the tables (the objects containing your data) in one accdb, which is usually referred to as a Back End.
  • Put the interface and logic objects (forms, reports, queries and VBA) in another, which is usually referred to as a Front End.
  • Link the Front End to the tables in the Back End.
  • Place the Back End accdb on a network location where all of your users can connect to it, i.e. in a shared folder.
  • Copy the Front End accdb to the computers of each user.

Another option, if you must share the relational database application with users outside your network, is to place the tables in a remote SQL Server or SQL Azure database, i.e. a "cloud" location to which all users can link. If you go this route, of course, the Front End accdb links to that cloud database and each user gets their own, individual copy of that Front End.

There are other options, but we'd need more information on your business environment to know if they would be appropriate.

Thanks @George Hepworth , today we are sharing via our internal network/folders and only internal users, not externals. The way it is today I would be happy to be able to share at list the reports, I was thinking Power BI could be a solution.

@Cleo Otterbech For reporting, Power BI is a great choice, IMO. Of course, that implies you have a site where the PBI files can be published, e.g. an O365 site.

 

 

@George Hepworth Hi again,

I have build my Access database with tables and linked tables from SharePoint. An I am also using this data to build a PowerBI Report to share the data with the organization.
We are sharing the application on a internal shared location, but what I see now is that when one open the database it is locked by the user even know none table is being used. Does this has something to do with the linked tables from SharePoint sites?

@Cleo Otterbech 

I'm not quite sure how you are sharing the relational database application, but if you mean that you have ONE copy of the Access accdb, and everyone is trying to use that same accdb, then that is the problem.

Each user MUST have their own copy of the Access accdb, all of which are linked to the tables, or in this case, the SharePoint lists.

Each user's accdb, which we call a "Front End" or FE, should be on their own computer, not in a shared location. 

That "split database" protocol is the only safe way to share a relational database application among two or more users.

 

The problem won't be related to using SharePoint lists, though.