How to connect Access 2010 to SharePoint and SQL Server 2008

Hey guys!

I was wondering..... Can I use an Access 2010 template which is linked to a SQL server Database to somehow link it to SharePoint Foundation 2010 and then SharePoint back to the SQL server where all the data from both the Access 2010 and SharePoint Foundation will be stored? Meaning that the Sharepoint will have some kind of a form just like the Access template and then it will be able to see the SQL server and sync all data back and forth...... Let's say I would like to see or enter a new record using any browser..... I would be able to see the same forms and data that the Access template has and when I enter I new student information it would be stored in the SQL server and show this new student on the Access template on my computer and Sharepoint too...... SQL server, Access 2010, and SharePoint Foundation 2010 would sync back and forth.... Is this possible? Is there a way to do this?



Thanks a lot!
hugonietoAsked:
Who is Participating?
 
hugonietoAuthor Commented:
I was able to make SQL server, Access 2010, and SharePoint Foundation 2010 talk to each other.  i was able to link an Access 2010 to a SQL SERVER and then have SharePoint to be linked to SQL SERVER as well. I was able to do this mainly in SharePoint by using SharePoint Designer 2010 and using the External Content Types connections. Even though the list views or styles on the SharePoint website to access the database looks very cheap and very simple..... it makes the point  I wanted!! to Syncronize between SQL Server and Access 2010! What I do in Access I can see it in SQL Server and SharePoint, What I do in SharePoint I can see it in Access and SQL Server, and what I do in SQL server I can see it in SharePoint and Access!!

Now, I need to find out how can I modify or make the SharePoint views of the database more interactive and more like Forms just like in MS Access!! That's going to be hard to do!! I can edit and create new records in SharePoint but still not good enough for the kind of interaction I want to do when accessing and editing records :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is this possible? Is there a way to do this?>>

 I was waiting for someone else to jump in who may have worked with A2010 and SharePoint directly (I have not), but from what I know of Access 2010, the answer is no.

 A2007/2010 use Sharepoint to store data for web based DB's.  Web DB's and what you can do with them was fairly limited.

 A2013 (which got a complete overhaul in terms of the web) is a lot closer to what you want, but still does not get your there.  While it now uses SQL Azure as a backend to store data (rather then SharePoint lists), which you can access with a traditional Access Desktop client database, what you can do with a web app is totally different then what you can do with a desktop/client app.

 The web apps in A2013 only support a small range of controls and events and there is no VBA language support.  There's no way to get the same type of UI in both web app and client app, but they can share the data.

Jim.
0
 
hugonietoAuthor Commented:
Thanks a lot JDettman!

Access 2010 has this feature that let you send the data or all tables to a SharePoint website. I tried this with the template I have been playing around. I works fine! once the data is sent SharePoint creates all the tables and creates forms based and the Access template and creates a link as well. So, the Access template synchronize with SharePoint back and forth but the data is kept in the computer where the Access template is (The Access File). It would be great if it could be sent to the SQL server..... I tried to make them synchronize (SQL server, Access, and SharePoint) each other when I found out I could link the Access template to SharePoint by linking the Access template to SQL Server too but I couldn't do it... it only synchronize between Access and SharePoint but I can't see the data in the SQL server.

I guess I can't make the three to work together!! Thanks for the info anyway!!



Thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<What I do in Access I can see it in SQL Server and SharePoint, What I do in SharePoint I can see it in Access and SQL Server, and what I do in SQL server I can see it in SharePoint and Access!! >>

 I really didn't think that was possible.  Make sure you accept your own comment as answer.

Jim.
0
 
hugonietoAuthor Commented:
I was able to make SQL server, Access 2010, and SharePoint Foundation 2010 talk to each other
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.

All Courses

From novice to tech pro — start learning today.