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!

 

[SydneyGeek]

You can use Sharepoint as the data store, with Access as the front end. Here is Microsoft's take on it: http://office.microsoft.com/en-us/a...access-and-a-sharepoint-site-HA010131463.aspx

There are limitations. One of the most widely published is that Sharepoint lists have a size limitation of 2000 items (for viewing; I read elsewhere that you can have millions of list items if you do it correctly).

If you can host an Access database on the network, another option is to split the database into a front end (queries, forms, reports, code) and back end (tables). Put the back end database in a shared directory and give each user a copy of the front end database. They will be able to simultaneously update data; databases by default are multi-user applications. DO NOT try to link an Excel file to Access and then expect multiple users to be able to use the system. As soon as you open the database you will lock users out of the spreadsheet, and vice versa.

Before you just push the spreadsheet into a database, take a serious look at how it's organised. Databases are very different from spreadsheets. You will need to plan the design thoroughly and get the data into Access; then check that it is consistent with the original by running some queries and comparing it with the spreadsheet. Go through this document to understand how to design the tables; there are plenty of other good resources out there too.

Denis

 

[ShieBoon]

Hey Denis, thank you so much for your effort in replying. I will certainly give it a try. Anyway, when you say "You can use Sharepoint as the data store, with Access as the front end", does that mean i should create a totally new database in Access and discard the Excel one?

 

[SydneyGeek]

The database will need to be in Access. Excel can be used to read data from the sharepoint lists once the Access tables are migrated.
But be warned; this is not a great way to go if you have large amounts of data. How many rows do you have in your spreadsheet at the moment?

Denis

 

[ShieBoon]

I see. I don't have many at the moment. Less than 500. Why do you say so? Is it because it will take a long time to read data from access?

 

[SydneyGeek]

It turns out that there are some alternatives. Look at this article.

Denis

 

[ShieBoon]

It turns out that there are some alternatives. Look at this article.

Denis

Hi Denis, once again, thank you for your effort to research on the question. I have read the article and shall try it out. Cheers!

 

[wheat]

It turns out that there are some alternatives. Look at this article.

Thanks for the link, Denis. I'm the author of the article in question; I hope it helps some people. I saw some traffic from this site in my referrer logs, and decided to sign up. I've used Mr. Excel in the past, but I had never joined the forums. Seems like a nice place. I think I'll stick around a learn something.

We've been using this strategy (front-end in SharePoint, back-end on a shared drive) where I work for a while now without a hitch. The only real issue has been that people insist on opening the front end for "checking out and edit," when what they need to do is open it read only. This should only be a problem when using document libraries which have versioning enabled (as ours do). It's counter intuitive to end users that they can open as read only yet make changes to the data. But that is exactly the case (since the data is stored on a networked drive, rather than in the document library).

 

[SydneyGeek]

Hi, welcome to the Board and thanks for the feedback / update.

With the reach of Access expanding into Sharepoint it's good to have people contributing in that area. Hope you stick around!

Denis

 

[peace]

Thanks for the link, Denis. I'm the author of the article in question; I hope it helps some people. I saw some traffic from this site in my referrer logs, and decided to sign up. I've used Mr. Excel in the past, but I had never joined the forums. Seems like a nice place. I think I'll stick around a learn something.

We've been using this strategy (front-end in SharePoint, back-end on a shared drive) where I work for a while now without a hitch. The only real issue has been that people insist on opening the front end for "checking out and edit," when what they need to do is open it read only. This should only be a problem when using document libraries which have versioning enabled (as ours do). It's counter intuitive to end users that they can open as read only yet make changes to the data. But that is exactly the case (since the data is stored on a networked drive, rather than in the document library).

How would it work if we have an Excel VBA application as front end to the Access database back end. Having both Excel and Access in the SharePoint list on a networked drive instead of a document library? Need special SharePoint configuration for that setup? Would that work with SharePoint 2010 and Excel, Access 2010?

Thanks.