Integration of Access, Excel and Sharepoint possible?

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
icon7.gif
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! :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top