Possible connection between excel, access, and sharepoint? (And other questions)

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
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.

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/e...ons-to-use-access-with-excel-HA010264081.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! :)
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,099,379
Messages
5,468,266
Members
406,578
Latest member
Julez_86

This Week's Hot Topics

Top