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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
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
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
fyed,
If I am way off base with my post above, let me know...
;-)
Jeff
If I am way off base with my post above, let me know...
;-)
Jeff
@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?
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?
<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
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
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
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
http://workerthread.wordpress.com/2010/01/16/thoughts-on-access-services-in-sharepoint-2010/
http://msdn.microsoft.com/en-us/library/office/ff402351.aspx