Solved

How to connect Access 2010 to SharePoint and SQL Server 2008

Posted on 2013-06-25
5
513 Views
Last Modified: 2013-07-03
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!
0
Comment
Question by:hugonieto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 57
ID: 39277824
<<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
 

Author Comment

by:hugonieto
ID: 39279221
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
 

Accepted Solution

by:
hugonieto earned 0 total points
ID: 39282031
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
 
LVL 57
ID: 39282083
<<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
 

Author Closing Comment

by:hugonieto
ID: 39295974
I was able to make SQL server, Access 2010, and SharePoint Foundation 2010 talk to each other
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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