Has anyone done this before?

[Lightwave]

In a number of my applications I have been thinking that I would give users access to a read only view of raw data. This would allow them to cut and paste to excel and would mean the one off tinkerers can tinker to their hearts desire while not on mass deleting / altering data.

Has anyone tried this before?
Any conclusions on it?
Anybody else thought of this before?

In complicated systems it is difficult to predict the strange and wonderful ways in which people will use the information and certainly in really rich applications it becomes impossible to design the shear number of reports people seem to want. Many users obviously tinker in Excel and I was thinking that if they could see the information in raw form (not edit or delete but be allowed to copy out and paste into excel) it could vastly improve users understanding of the underlying data.

I know it's a kind of break from the normal tenants of design whereby users don't get to see the data but sometimes good design really hides data structure. Especially for users not yet acquainted with normalization.

I feel sure if people would gradually use the raw data in excel files they would gradually get an excellent understanding of the concept of normalisation and relationships which would feed through to a far better informed user.

 

[spikepl]

There is no law against displaying raw data. Or giving read-only access. You can do that via the datasheet view of forms, so what is the problem?

Nor is there a law against giving people access to any objects you like. Only common sense and experience dictates that that might not always be the best thing to do.

 

[plog]

What I normally do is create a reporting menu with a drop down of reports, various criteria they can input to filter by and then an option to either view the report or export the underlying data of that report to a spreadsheet using the TransferSpreadsheet method. Then when they click the 'Generate Report' button it either displays a print preview of the report in Access or exports their data and opens it up in excel.

This way I don't have to do any fancy form work to make it read-only and automatically gets them out of Access and into Excel so they can play with the data.

 

[vbaInet]

I think Lightwave is after best practices in relation to his OP as opposed to "how to make a recordset read-only". It's easy enough to set the RecordsetType property of a form in Datasheet view to Snapshot and that will make it read-only.

@Lightwave: It's up to you however which way you would like to present data but there really should be restrictions as to what they can see. I've not seen any database that gives "normal" users a view of the raw data (i.e. IDs and keys) but I've seen users with Admin rights having this view.

 

[spikepl]

One point: Consider a properly normalized DB, not using any table lookups, where many fields are keys. Such data would seldom make much sense to anyone.

 

[Mr Smin]

One point: Consider a properly normalized DB, not using any table lookups, where many fields are keys. Such data would seldom make much sense to anyone.

With this in mind, you could consider allowing selected users to design their own queries - but only allowing Select permissions on the tables in order to prevent them changing underlying data.

 

[vbaInet]

With this in mind, you could consider allowing selected users to design their own queries - but only allowing Select permissions on the tables in order to prevent them changing underlying data.

So now they get to see all the tables?

 

[spikepl]

I think the entire discussion is predicated upon the assumption of users having an interest in all this. My bet is, that other than in some engineering or development environment, nobody gives a damn, because everybody is busy getting their job done. Providing new paths to data then just equals confusion. And "normalization" probably has as much general interest as the name of the weed-killer in the company's garden.

 

[Lightwave]

I think the entire discussion is predicated upon the assumption of users having an interest in all this. My bet is, that other than in some engineering or development environment, nobody gives a damn, because everybody is busy getting their job done. Providing new paths to data then just equals confusion. And "normalization" probably has as much general interest as the name of the weed-killer in the company's garden.

and therein lies my mistake

I'd be interested so I assumed others would be as well.

 

[gemma-the-husky]

What I do in some cases:

obviously there is a limit to how many versions of queries you can provide. In Access, we actually limit the abiliity of users to do stuff themselves, because of the possibility of them damaging the data

So i tend to provide a "dump data" option that assembles some key tables into a single query, and lets them save the whole query, as a csv/spreadsheet - then they can examine and filter their data any way they want.

also, you can always let them open any query in readonly mode, and they can just save that to excel as well

 

[BBQ Kittens]

I was having a problem where individuals were constantly asking for unique results and odd calculations that were very simple using a query but were one time deals. my solution was to create a form that acted as a query. They can select the table which then allows the selection of fields and then allows the use of sum, max, min, etc. and allows users to insert criteria. This allows them to generate a query of data specific to their interests which is deleted on close. If your interested I can post it and perhaps you can modify it to your needs. With a little work it could be pretty flexible.

 

[Lightwave]

Thanks BBQ mine was more of a thought than a requirement

 

[RainLover]

and therein lies my mistake

I'd be interested so I assumed others would be as well.

I though you got some good responces.

But if you are not interested, -----------------------