| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2003-12-23 : 09:41:49
|
| The system I run has SQL server 7/2000 back end and uses an MS Access database with linked tables to allow reports to be created. The problem with this setup is that it is very easy for someone who doesn't know what they are doing to 'accidentally' change the reports or something else in the Access database without anyone knowing about it. In addition this file is now suffering from serious bloat and is in danger of exploding!The people who are creating the reports are relatively unsphisticated in that they can write queries and create reports in Access but are certainly not programmers in any sense. What I want to know is what should I look at which will allow them to create reports but allow me to administer them so that only authorised users can alter them/create them etc. I know a bit about Crystal Reports and I've read about a SQL server add on that Microsoft are going to introduce but is there anything else I should look at or consider?thanks in advancesteveSteve no function beer well without |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-12-23 : 10:19:52
|
| There is Crystal Reports as you have said. I have worked with Cognos Impromptu, and MicroStrategy. In one systems group I worked with an MIS department that had coders to build TSQL reports and used DTS/VBA to export them to excel/presentation program. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-23 : 10:27:31
|
| Access does have security, but it is kind of clunky to implement in that everyone must join a workgroup or you must open Access w/ some command-line options. But it does work, and it does all the things you are asking. Read about it in Access Help, and/or get a good Access book that explains it. It is complex, but it completely allows you to control who can do what to all objects in a database.- Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-12-23 : 14:21:05
|
| Best thing you can do is keep a clean, untouched, reference copy of the database with the reports in a working state. All tables should be linked to another database or SQL Server. Have the users work on a copy of this database. If they blow something up, just delete the file and copy over the fresh one. If someone loses something that did work, well, too bad, they should submit changes to you so you can include them in the reference copy. This is basically poor-man's source control, but it should work for you.Although Jeff is right, my experience with MS Access permissions has never been anything but pure torture, and I ended up locked out of my own DB's a number of times. If you go down this path, make sure you keep an unlocked copy of the database as a reference at all times. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-23 : 16:05:41
|
lolThe visual alone...BANG...BANG...BANG...Let me in you FOOL...I created you! Brett8-) |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-01-05 : 09:12:28
|
| Thanks to all for the suggestions, I'm afraid my experiences with Access security are not good which is why I am trying to get away from itcheerssteveSteve no function beer well without |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2004-01-05 : 11:27:33
|
| If you build your reports in Crystal and then give your users a viewer only (cView), you can avoid the issue. The viewer is a lot cheaper than Crystal and allows them to do parameters and have print control. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-01-07 : 10:30:58
|
| It sounds to me as though Crystal will be the best optionthankssteveSteve no function beer well without |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2004-01-07 : 21:56:27
|
| I have read that SQL Server is coming out with a reporting service soon that will add some competition to the less than ideal reporting market. I believe it is now or soon will be beta. I'm looking forward to evaluating it when available. |
|
|
|
|
Reporting Issues