Differences and advantages to SQL and SharePoint upsizing

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
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
SteveL13Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
SteveL13Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
PatHartmanConnect With a Mentor Commented:
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
 
SteveL13Author Commented:
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
 
PatHartmanCommented:
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
 
SteveL13Author Commented:
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
 
PatHartmanCommented:
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
 
SteveL13Author Commented:
Will do.
0
All Courses

From novice to tech pro — start learning today.