Sharing MS Access data in the corporate environment?

[jonnycakes]

Hello everyone,

I've found myself in quite the predicament.. I've built a data entry access datbase using access 2000. I've split the database into a front end and backend, the backend is stored on a shared drive while the front end is utilized by approx 50 people to enter data.

I've built a very basic website that includes a link to a batch file that automates the installation process for the front end users.

Now, I need to find a way to distribute the data to an additional 70-80 people, but I want to keep these people out of the database to ensure the database doesnt take a hit on performance.

The additional 70-80 people that need the data must review the records that are assocaiated with their names(approx 30-35 records per month, 6-7 per week).

I've got access 2000 and excel 2007 at my disposal, but I do not have sharepoint, sql server, or many other tools.

I want these additional 70-80 people to be able to review the data to see how well their teams are performing month over month as well as comparing themselves to their peers. I could accomplish this by simply providing a shared workbook, but excel prevents users from running macros(which I need for my pivot tables); excel also prevents me from updating my data table via an access query.

Are there any BYTES.com gods out there that might be able to guide me in the right direction?? Thank you in advance for any insight you may be able to provide.

[zmbd]

So, basically all they need is a report, they don't need to actually log-in to the database, is that correct?
-z

[jonnycakes]

They need a report with the option to flag each record as reviewed. Currently, they have a drop-box with "Y" or "N" (excel) as options to signify whether the record has been reviewed.

I have several problems with this. Problem 1: Shared workbooks do not allow me to easily update the raw data on a weekly basis or run macros. Problem 2: When distrubting individual files via email I have no way of knowing what was and was not reviewed in the previous week. Problem 3: This file is only going to continue to grow and with that being said distributing via email will more than likely waste bandwidth.

Please let me know if you have any further questions. Thank you for your time.

[jonnycakes]

I feel very stupid right now.. I don't know why i didn't think of this sooner, but I decided on splitting up my report into two seperatatle reports. One report will be a shared workbook that merely provides the raw data as well as provide the end user with the option to flag each record with a simple list box. .

The second report will not be shared, but rather a read only report. This report will contain all of the data analysis, and a direct odbc connection to my access query. This will allow me to control what records are available at what time. I will tie in some vba to copy my data from this report into my shared workbook.

I've included hyperlinks to link both files for easy access.

I'm sure there may be several alternatives that would better suit my needs, but I'm satisfied until I cross that path. I hope this helps somebody out there.

[zmbd]

Sorry it's taken me so long to get back to this a lot of other projects popped up, it happens, life is good!

Which version of Access are you using?

Now I haven't done this; however the following might be worth looking at - some guidance from the "Masters" about this suggestion is certainly worth waiting for:

- http://office.microsoft.com/en-us/ac...010015427.aspx

- http://www.dailymotion.com/video/x9p...al-data_school

This is something that appears to be new in MSA2007.

Something else to consider is a sub/mini database... what I do is take the subset of data requiring review, push that to a template database that has my logic in it and then compile it to an MDE if they have MSAccess or to a "runtime" if they don't (runtimes require a lot more care in their creation); however, I only do this for very small record sets, like a few dozen to a few hundred.