Advice about creating an online access database


I've been asked to create an MS Access  online quote system.  I am thinking that I can create an desktop access application and then publish it to Sharepoint where it will run in an intranet environment.  Currently the client just has Access 2007 and I've just asked their IT department if Share Point Access Services is installed.  What other questions should I be asking?  I've never worked with Share Point and I don't want to design an application that is incompatible with share point
Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
With Access, there's nothing else other than putting it on a Terminal Services server and offering a connection to that through a browser.   If you do that, then you'd run Access on that server, not through SharePoint.

The web databases in A2007 and 2010 have been replaced by Web Apps in 2013.  Still through SharePoint, but now runs on a SQL Server back end for the data.

  2007/2010 use SharePoint Lists to store data, which can have performance issues with as little as 5,000 records.

  If the concurrent users is low, you might also consider   It a subscription service however, so cost can go up quickly if you have a lot of users.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also note that both web databases and web apps have limited capability; basically CRUD (Create, Read, Update, and Delete) operations on records.

 There's no VBA (must use macros) and your limited in the controls and forms that you can use, which is why most go the terminal services route if they want what they have in the desktop client.  

DO NOT attempt this with Access 2007 and old versions of SharePoint.  That technology has already been deprecated and there is no upgrade path.  If you want to attempt an Access web app, use A2013 and the latest version of SharePoint.

The first question is why must the app be online?  Will remote users be using it?  Will anonymous users be using it?  If not, a standard client/server app is the best option.

If you attempt to create an Access web app - BE WARNED - no VBA code is allowed and many functions are not supported such as reporting and emailing.  So, do your due diligence before you make any commitment.    If all users are known and you can have them install the Access FE, you can attempt a hybrid app.  This is a normal client/server app EXCEPT that the tables are linked to Azure.  The latest version of SharePoint is required as well as A2013 but the app will be fully functional.  The only problem may be that remote users experience slow response.

The best solution for allowing remote users is Citrix.  It doesn't require ANY application modifications and remote users do not experience any delays.  In fact, they may actually have performance superior to that of local LAN users since the app and the database are normally stored together on the Citrix server so there is no latency.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Juan VelasquezAuthor Commented:
I just received some new information. The access application will be accessed via the company intranet
Juan VelasquezAuthor Commented:
I've been doing some more research and I understand about web apps not using vba.  If the entire database(containing vba) is migrated to a share point site, would vba code still be available and would users require Access Runtime to run the application.  The application will allow users to enter data, which is run through calculations to produce a quote.  
I also haven't committed to anything.  I want to find out if what the client wants is technically feasible and is a practical solution before I say "Sure thing, I can do that"
This is a greenfield project.
If it REALLY needs to be online, as in 'internet-available', then really the only use for Access is as a rapid development platform for the database and business logic.  You can knock together the data storage and program flow in Access more rapidly than in any other platform.

Once you've got that, though, your use of Access is at an end.  Pick a Web technology: C#, Python, Razor, Scala, Ruby-on-Rails, various JavaScript frameworks or something else with a reasonable learning curve and broad base of community support, and develop your application in it.  MS doesn't say so, but their recent moves make clear that SharePoint is being relegated to the second-tier.  It's all about Azure now.  The Access-SharePoint offering was never very good, and the lack of VBA support meant that it achieved about the same mindshare as Windows Phone, for better or worse.

Start looking at the dollars-and-cents of Access & SharePoint licensing, and unless those are already sunk costs for the organization, I doubt there's much incentive to drop the cash necessary to tool up on a platform that it is clear MS no longer views as strategic.
To use VBA requires the desktop Access run-time at the least.  You then wind up with a bifurcated app.  What gets published on SharePoint as the WebApp CANNOT use VBA.  You can create a parallel desktop app that can, and it can access the same datasource as the SharePoint app.  A rich client and a poor cousin.

But anyone wanting to use the desktop client needs very good connectivity to the server hosting the data.  Slow, laggy WAN links are verboten for the desktop app.

And the functionality of one will not match the functionality of the other.

This link may interest you, Jim and Pat
Juan VelasquezAuthor Commented:
Hello Pat,

I'm just thinking that perhaps, given the current requirements and constraints, that the best solution might be hosting the application on a citric server and putting a link to the application on the company intranet.  Hosting the Access app would require Access runtime and the web app approach cannot use vba.  Based on the requirements that I have, I'm not even sure if I have to use vba.  In addition, since it is an online quote system, there could be any number of users and Access has a practical limit to the number of concurrent users.  These are just the thoughts I have.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<This link may interest you, Jim and Pat>>

  Yes and no...old've always been able to "hybridize" your Access apps, but that's not the correct term.

 This is nothing more then client/server through Office 365.  You've always been able to put a SQL Server where you want and connect to it.

 A hybrid app would be adding a web app to access the same DB.  Now it's a "Split FE" and a Hybrid when taken as a whole.

Juan VelasquezAuthor Commented:
After thinking it over, and given the system requirements, I've concluded that the best approach is to develop the application exclusively in share point.  I've been given access to the share point server along with the time to learn share point.  Although Microsoft always says that Access is fully compatible with share point and that the migration process is easy and painless, experience has shown me otherwise.  In addition, since the application will be used exclusively from share point, it makes more sense to design in share point from the start.
SharePoint is not a relational database.  SharePoint lists will be very slow for more than a few thousand rows.  this is not a good idea.

If you must have something accessible via the web, go with a web technology and don't use Access at all.
Juan VelasquezAuthor Commented:
Hello Pat,
I will not be using Access st all. I will be designing the application just as a SharePoint app.  The output will just be a single row
OK, your question was in the Access topic so it sounded like you wanted an Access solution.  Good luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
I originally was going to use access but thought better of it
Juan VelasquezAuthor Commented:
Thanks everyone, I really appreciated the feedback as well as the links.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.