Solved

How to connect Access 2010 to SharePoint and SQL Server 2008

Posted on 2013-06-25
5
505 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now