Solved

How to connect Access 2010 to SharePoint and SQL Server 2008

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 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