Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Differences and advantages to SQL and SharePoint upsizing

Posted on 2015-01-19
13
Medium Priority
?
150 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40558745
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
ID: 40558789
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 85
ID: 40558854
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 total points
ID: 40559149
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
ID: 40559693
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 52

Expert Comment

by:Gustav Brock
ID: 40559749
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
 
LVL 85
ID: 40559784
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 40

Assisted Solution

by:PatHartman
PatHartman earned 664 total points
ID: 40559810
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
ID: 40560429
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 40

Expert Comment

by:PatHartman
ID: 40560464
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
ID: 40567242
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 40

Expert Comment

by:PatHartman
ID: 40569500
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
ID: 40569507
Will do.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

824 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