• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

MS Access and Sharepoint

Hi

I have an Access db that users need to use over the web.  I understand that you can put an Access db on Sharepoint.  How does this work?  For example does this still work in the normal 2 tier structure and would users see the Access forms in the normal way.  Also are there any limitations - eg does VBA work in the same way?

Many thanks

Ken
0
kenabbott
Asked:
kenabbott
1 Solution
 
Yagya ShreeCommented:
Take a look at this MS post

Introduction to integrating data between Access and a SharePoint site
http://office.microsoft.com/en-us/access-help/introduction-to-integrating-data-between-access-and-a-sharepoint-site-HA010131463.aspx
0
 
Jamie McAllister MVPArchitectCommented:
Further see this the information below. Do you have Enterprise Licences for your SharePoint Server? Access Services is an Enterprise feature...

http://workerthread.wordpress.com/2010/01/16/thoughts-on-access-services-in-sharepoint-2010/

http://msdn.microsoft.com/en-us/library/office/ff402351.aspx
0
 
Rainer JeschorCommented:
Hi,
this feature is called "Access Services" and you need to have SharePoint 2010 Enterprise Edition (!).
The access forms will be "migrated" to Forms pages in SharePoint, reports will be converted to SSRS reports and data (tables) will be stored in lists.

Access Services wont support VBA code, linked objects ...

Good overview:
http://www.slideshare.net/patenik2/access-services-in-sharepoint-2010-all-you-need-to-know

HTH
Rainer
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Dale FyeCommented:
You don't state what version of Access or Sharepoint.

I have a number of Access applications that use Sharepoint lists as the data "tables" and just link to these tables.  I send out updates to the Access applications or drop them on the portal for people to download.  Then they just run them as they would any other Access database with an external data source.

I've experienced some latency at some of my remote sites, but I have about 16 offices spread across the US that are accessing their data this way.  One downside is that SharePoint (at least 2007) does allow you to create referential integrity between "lists", so you will have to build some error checking into your applications to prevent orphans and invalid entries.
0
 
Jeffrey CoachmanCommented:
Just to be clear.

A LOT is involved for this to work.

In other words you just don't just click a "Use Access on SharePoint" button and this all works just like it does on a desktop system.

"I have an Access db that users need to use over the web."
For Users or Customers?, ...or both?
Clearly define "use over the web".

The key word with Sharepoint is "Share". (Collaboration)

So if your term "Over the web" is for customers to log in an see their data, then this may not be a job for Sharepoint.

Are you aware of all the resources needed to do this properly (Training, Hardware,Software,...etc)

Not trying to be a spoilsport here.

It's just the the MS documentation will say something like:
 "You can use SharePoint and have your data available for use on the web"
...but not quantify all that is involved to do this...


JeffCoachman
0
 
Jeffrey CoachmanCommented:
fyed,

If I am way off base with my post above, let me know...
;-)

Jeff
0
 
Dale FyeCommented:
@kenabbott

In my earlier post, I may have made it seem "really easy".  While not as difficult as Jeff implies, there are some issues.

1.  It is easy to use the "Export" feature of Access to export to a list in SharePoint, but unless you restrict access to the folder where those lists are located, anyone with access can simply open a list and change a value.  Once you have migrated the table to SharePoint, it is also easy to link that list back to your Access application

2.  If you don't have  a primary key assigned for your tables (even if you do have an autonumber field) Sharepoint will assign one and give it the field name "ID" when you migrate your data to SharePoint, so make sure that you have a PK in every one of your tables.

3.  Because Sharepoint uses SQL Server, you will not see the primary key value of your table when you create a new record.  This value will not be assigned until you actually save the record.  When I first encountered this, I could not figure out what was happening, and it took me a while to figure it out.  This is not a big deal, but I was trying to do something prior to saving a record, and unless you are aware of this, you can pull out a lot of hair.

4.  You don't get the advantages of using views and Stored Procedures that you would get with a SQL Server backend.

If your goal is to use Access as your front-end to avoid converting everything to .Net or some other web platform, then SharePoint works relatively well as a backend.  But you could similarly use SQL Server and get the advantage of relational tables, stored procedures, and views.

There is also another alternative, but I cannot recall the name of the service off the top of my head.  Jeff, you remember the name of that service that allows you to basically store your access data in the cloud?
0
 
Jeffrey CoachmanCommented:
<While not as difficult as Jeff implies, >
Yes, again, sorry if I sounded like it was "difficult"

My point was that it may not be as simple as some of the MS docs might imply.

;-)

Jeff
0
 
Jeffrey CoachmanCommented:
fyed.

I think it was using "Azure"?
http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

I know Office 365 does some "cloud" stuff, but interestingly MS Access is not mentioned in the promo video:
http://www.microsoft.com/en-us/office365/free-office365-trial-si.aspx?WT.srch=1&WT.mc_id=PS_google_Office+365_Entice_office%20365_Text
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now