Integration of Access, Excel and Sharepoint possible?

[ShieBoon]

Possible connection between excel, access, and sharepoint? (And other questions)
<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->Hello all,

I'm a new user whom just registered yesterday. I'm currently using Excel 2003, Access 2003 and Sharepoint 2007.

Overview of the current situation​

At this moment, i have all my data stored in Excel like a 'database'. This Excel file is available in a staff Intranet, deployed on Sharepoint. My colleagues accesses the Excel file from the Intranet to update information, and saves the changes after updating to reflect the changes.

After a period of time, inconveniences and loopholes are beginning to surface as my colleagues' needs increase.

Problems​

I am considering to use Access as my database instead of Excel for a more organized database.

Because I am restricted to only use Sharepoint, can i actually integrate Excel, Access and Sharepoint all together? I heard that it is possible to connect Excel to Access, and then apply Sharepoint technologies with both. But i'm wondering whether it is a good decision. And if so, are there any major limitations that i should take note of?


My colleagues are unable to update information concurrently

Currently, my colleagues access the Excel file from the Intranet (Sharepoint) to do some updating of information. Let's say there are two colleagues, Colleague A and Colleague B.

1. Colleague A accesses the file from the Intranet and starts to update
2. Colleague B tries to access but realise that he is only able to view it in a 'Read-Only' mode
3. Until Colleague A successfully 'disconnects' with the excel file then can Colleague B access it.

So basically, only one user can access the file for amendments at one point of time.

I was wondering, if i connect Excel and Access together, use Access as a database instead of Excel, and then connect both Excel and Access to Sharepoint, would this allow 2 or more users to perform updates to the Excel file in the Intranet?

Because i read the following at this website:
http://office.microsoft.com/en-us/ex...010264081.aspx

Both Access and Excel provide commands to connect to data in Microsoft Windows SharePoint Services lists. Excel 2007 provides a read-only (one-way) connection to linked SharePoint lists; whereas Access 2007 lets you read and write (two-way) data in linked SharePoint lists. Access and SharePoint lists work very well together. New Access and SharePoint data types — such as rich text, append only (to support tracking revision history in a memo field), attachments, AutoNumber, lookups, and multi-valued fields — make for smooth integration and few, if any, data inconsistencies.
Once you publish your Access database to a SharePoint site, you can even take the SharePoint list data offline in a local Access database, work with the data locally, and then reconnect to the SharePoint site to upload any changes. Any conflicting data changes made by other users will be handled by a conflict resolution wizard. The forms and reports you created in Access are based on the same data, but linked to SharePoint lists and can be opened directly from Windows SharePoint Services.

Thus, might this (whereas Access 2007 lets you read and write (two-way) data in linked SharePoint lists) solve the concurrent updating issue?

That is all for now.

Thanks in advance and cheers!

 

[peace]

Hi Dennis,

Are you referring to this article: Introduction to integrating data between Access and a SharePoint site - Access - Office.com? This article didn't mention having the Access DB in networked shared drive vs. SP document libraries. What if you add Excel VBA as front end application to the mix of a Access DB back end? There must be SP pattern solutions to this kind of patterned application. Thanks.

peace

 

[JAS87]

Hi Denis,

I am also wondering about the functionality of a front end Excel VBA doc on SharePoint and a backend Access DB stored on a shared directory and possible implications of hosting the excel doc through SharePoint. I currently find that the updates to the excel doc on a SharePoint site do not correctly transmit to the Access DB and am wondering if certain features or functions must be enabled in SharePoint or Access?

I have Access, Excel, and SharePoint 2010

Thanks!

 

[SydneyGeek]

My understanding is that Access can both read and write Sharepoint 2010 data. Excel can read but not write.
You may need to look at a Sharepoint forum to get more depth, but maybe this will help: Top 10 reasons to use Access with Excel - Excel - Office.com look at point 10.

Denis

 

[mikeymay]

I appreciate some time has passed since this was last updated but I am having a similar issue with Office 365 SharePoint.

I have developed an Access 2016 Db that is queried by and updated from an Excel 2016 front end.

All worked as expected when developed on my desktop but I am now having a problem making a connection to the Db from Excel and I am making the assumption that a Sharepoint/Access/Excel combo doesn't work like an Access/Excel combo.

Any advice on how to resolve this issue would be hugely appreciated as I have spent a huge amount of time developing it to work on a desktop and I'm hoping it can be adapted to work on a sharepoint basis.


TIA