Solved

Differences and advantages to SQL and SharePoint upsizing

Posted on 2015-01-19
13
134 Views
Last Modified: 2015-01-25
I may be confused but what are the differences and advantages both ways between upsizing to SQL or SharePoint?  Sorry, I've never done either.

--Steve
0
Comment
Question by:SteveL13
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
Comment Utility
Upsizing to SQL Server basically takes your backend Tables and moves them to a SQL Server Database. You can also convert queries to Views, and move some processing over to Stored Procedures. The move to Tables is generally fairly quick and painless, depending on how diligent you've been in following the "rules" that SQL Server requires (i.e. no spaces or odd characters in table or column names, etc).

Moving to Sharepoint can entail different things depending on what your goal is. Can you describe a bit more about what you're wanting to do?
0
 

Author Comment

by:SteveL13
Comment Utility
Would like to have the ability to have a front-end file on each computer that will run the database and have the back-end data perhaps on a SharePoint site.  Need to be able to run the program from anywhere that has a connection to the Internet.
0
 
LVL 84
Comment Utility
Sharepoint is not what you're looking for. SP is more about document/content management and sharing.

You could purchase a hosted SQL Server from many places, or you can setup and host it yourself. It's tricky, and failing to properly secure your server will just about insure you'll be harvested almost immediately, so if you plan on doing the self-host find the money to have someone familiar with that sort of setup do the work for you.

That said: If you're looking "to run the program from anywhere that has a connection to the Internet.", then you've essentially just described a web application. I'm curious why you're using Access to do something like this.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
Comment Utility
Then you would probably be best fit with SQL Server on Azure:

http://azure.microsoft.com/en-us/services/sql-database/

It's quite cheap and will scale beyond what you will ever need.

/gustav
0
 

Author Comment

by:SteveL13
Comment Utility
First of all thank you to both of you for the input so far.  

Scott:  Then I have to wonder why MS Access 2013 shows SharePoint in the Move Data section.  This leads me to believe that if I do that, the backend tables will live on a SharePoint site and I'll be able to link to them with the front-end Access file.  Am I not correct?

Gustav:  I checked out the link you furnished and the whole Azure thing has me confused.  Is my own fault for sure.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
All you need is a Microsoft account.
From then on it's quite simple, in fact, and absolutely free for the testing which is not limited in features.

Administering an Azure account is not difficult but, of course, may take you more than two minutes to digest.

/gustav
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
Comment Utility
Just because you can do it doesn't mean it's a good idea :)

You can move your tables to Sharepoint if you'd like. As I mention, it was intended to be a document sharing platform, not a data sharing platform, and you'd be be contained to that "environment". Sharepoint is a web framework, which works best with web-enabled platforms (of which Access is not one). To each his own, of course - but don't say you weren't warned :)

2013 is better, at least to my knowledge - but you'd be much better served using a standard SQL implementation.

FWIW, I've tried using the Azure platform with Access, and the performance is pretty terrible. I've heard other reports of poor performance on that platform as well. If you decide to go that route, be sure to test with a reasonable user load.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
Comment Utility
SharePoint lists are not a relational database.  They are "lists".  A SharePoint list could be a useful way of allowing the user to do simple data entry over the internet or to view simple list type reports.  I would never keep application data in a SharePoint list.  Also, performance is poor with more than a few thousand rows.

If you want remote access to your app, you need to really think about why.  What is the need?  if your users will always be known ahead of time and "internal" to the company, then a client/server app would be best.  If you need the app to support remote users either from home or other sites, then Citrix is the best solution.  If the application needs to interact with the public then making an Access app isn't even a consideration.  You need a real web development product.

A2013 introduced Access web apps (earlier versions were deprecated already and not even worthy of consideration).  They have potential but are not yet ready for prime time.  You can do simple things that don't require VBA.  You can use macros but the options are limited.  If you embark on an Access web app development path, make ABSOLUTELY CERTAIN that the tool will support your requirements since there is much that it cannot do.  Don't get caught.
0
 

Author Comment

by:SteveL13
Comment Utility
This is going to be an order entry application.  Could be upwards of 4,000 records entered a day.  I guess I'm thinking Access in any way shape or form is not a good way to go,

Agree?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
If you have a staff of order entry clerks taking phone or mailed in orders, you can create an Access app that would work just fine.   this would be an in-house app and doesn't need to be web hosted so you can create the normal client/server app and link to SQL Server tables to handle the volume.  

However, if this is a web app where people place their own orders, then Access is not appropriate for this type of application (and I don't say that very often).  You can't be sending people an Access database FE to use just to place an order and the Access Web app hosted in SharePoint is way too limited.  Also, MS has changed the way SharePoint plans work and so unless yours is already in place, the new plans don't allow anonymous access so it is useless for an application where strangers come to place orders.

Depending on what you are selling and whether or not it is customizable, you may be able to simply purchase a product off the shelf and upload your catalog.  The boxed apps work very well for storefront apps where you have standard items for sale with possibly size and/or color options.  They don't work for custom products where the user has to make a lot of decisions on how to configure his purchase.  If you require customization, you will need to write your own using ASP or something like that.
0
 

Author Comment

by:SteveL13
Comment Utility
I have a MS account.  I have setup a SQL Azure database.  But when using the Microsoft SQL Server Migration Assistant for Access and attempt to migrate I get an error... "There is nothing to Process" even though I checked the tables in the access d/b
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Steve,
Please start a new thread with the specific question.  This one is about opinions rather than specific problems.

I haven't run into this problem.  It may be that your tables cannot be converted because they contain something that Azure cannot support.  Start by doing one table at a time.  If you have an older version of Access, try using the upsizing wizard also.
0
 

Author Comment

by:SteveL13
Comment Utility
Will do.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now