Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

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
0
SteveL13
Asked:
SteveL13
  • 5
  • 3
  • 3
  • +1
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Gustav BrockCIOCommented:
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
 
PatHartmanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now