We're using MS Access 2003 for a administrative system.
In this system we're able to create several reports with data from the database.
One user figured out how to export a report to excel and is sending out excel-sheets with the data. We don't want this to happen and we are unsuccessfull in preventing him doing this.
So we would like to block the functionality to export to excel entirely.
How do we block exporting a report ?
I'm currently unable to make an mde-file from the database, probably because we have a little to much forms and reports. I would love to make an mde if this makes things more secure, but didn't succeed to do this yet.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Im not sure why your database wont convert to .mde ive never had a problem with that. Have you split it into front end and back end applications yet? That is always a good idea before coverting the front end to MDE.
The chances are he's doing file->export somewhere. You can also edit the toolbars in MS Access, possibly remove anything that the users don't require, or even remove everything if all the buttons that you require are on the forms and interface screens (if you have these.)
Also, any data that is presented in forms can usually be straight copied/pasted. I've prevented this in the past by capturing the focus property of subforms/elements and immediately calling a macro that shifts the focus elsewhere. This wouldn't work on reports however, but I think converting to mde and disabling toolbars should stop anyone being able to get to the source query of a report.
Matt
>> Shut down the menus for that user
Tried it, seems to be a good idea, the user won't be able to find out anymore how to do it.
We can simply remove all expert functionality from the menu's, copy and paste still needs to be there though.
Access itself reports that it can't make an MDE, probably because the database is too large. The database is split up in a data-file and an application-file.
Also, it isn't a problem if the user copy's 1 single field of data and does something with it. What we need to prevent is that a large portion of data is exported to excel or something similar.
I know it is probably a better idea to educate the user, but that has been tried and failed. Seems to me that the only remaining option is to block the functionality for the user so he isn't able to do that anymore.
You can implement ULS and do this, but it's not a simple thing to do. With ULS (User Level Security) you can disallow ALL table access, which would therefore disallow your user from exporting to Excel. However, as others have mentioned, you might be able to do this simply through UI considerations (i.e. removing menus, setting startup options, etc). I'd try this method first, before considering ULS.
If your code won't compile to .mde, it's normally because it simply won't compile ... the process of making the .mde file first Compiles your code. If that task can't be completed, then the process stop. You really, really should determine why this won't happen, even if you don't want to convert this to .mde. Open your VB Edit window and click Debug - Compile ... this will show you everywhere there is a code issue, and you can work through these and fix them.
And I'm with the others regarding this being a management issue. If this has been tried, and the user still won't adhere to company policy, then I'd speak with management regarding this and let them decide what to do.
Given the computer intellect of the user we're talking about, it should be possible to handle this through UI. I'm doing just that right now, with success I might add. All buttons to "export" links are now gone.
Is removing things from the menu-bars user based? this isn't stored in the application itself? how can I store this in the application itself? Or should I change the menu's manually on the users computers?
Very good suggestion on the MDE-file, I've compiled the code and found a couple of errors. After correcting these errors the application is converted to an mde without any problem!
I can't say any more on why education doesn't work this time. That has been tried and failed. The only way now is to remove the functionality. I do agree with you that in a normal situation this should be handled by a company policy, but then again, that doesn't work in every situation.
Sounds like an interesting situation!
As far as getting data out of tables goes, one method that hasnt come up is that if the database is accessible by a web server, then an ASP or PHP script could be used to get data out of tables. This could even be done by a user running a personal web server on his computer. I used to run an access database with a back end on the server, front ends on everyones machine and a web server with perl and php scripts would query it too and send data out to the internet at regular intervals.
I dont know the ins and outs of your situation, but in the name of complete solutions, this is probably worth mentioning too.
<Is removing things from the menu-bars user based? this isn't stored in the application itself? how can I store this in the application itself? Or should I change the menu's manually on the users computers?>
No ... this is application-wide. If you change menubars in your dev copy and then distribute that copy, then your changes are immediately evident. You can manipulate menubars and such through code, if need be.
You should also disable the Shiftkey bypass ... unless you do, any user can simply hold down the Shift key and undo all the changes you're making: http://www.mvps.org/access
So that you're clear: even with the ShiftKey bypass disabled, it's quite simple for a determined user to re-enable the shift key and do what they wish with the data. The changes you're making should work to discourage the curious, but if someone is determined to get to your data then there's little you can do about it ...
The number of forms and reports should have no bearing on the ability to create an MDE version of the database unless you get over 2000
Go into a module and do a compile from the Tools menu - if it won't compile, it won't create an MDE
Another reason is that it may be in Access 2000 format - try Tools/Database Utilities/Convert Database and make sure it is in 2002/03 format
DoppyNL,
<I can't say any more on why education doesn't work this time. That has been tried and failed.>
<that doesn't work in every situation.>
In my company, either you follow company policy, or you won't be a member of the company any more!
Transmitting company data to people outside the company (or to unauthorized users inside the company) is, technically, theft.
Your company has no policy on outright theft? They are powerless?
Meaning you asked him to stop, ...and he refused, ...and thre is absolutely nothing you can do?
:-O
JeffCoachman
@MatthewP:
It's a "normal" access database, no connections to the internet. It is only run on a local network directly in MS Access.
@LSMConsulting:
<No ... this is application-wide>
I changed a menu and removed a button for exporting to word or excel on the menubar "print preview" (translated). But when I open the application on another computer, the button is still there!
It looks like I need to remove the button manually on all computers. Wich means I have to do it about 8 times... Once would be nice though.
How would I do it only once? I can remove the complete menubar, but that one is needed for other functionality wich is on it.
Good suggestion on shift keys, don't think we need it. No user here would even know the option is there. I can allways do that later when it turns out it is needed.
@boag2000:
As I've allready stated, I can't see anymore on the matter other than that we need to find a technical solution to make it impossible to do. There are situation where this can happen, although that would not be very often.
You'll probably be better off building your own menubar and using that as the Default. You can do this by:
1) Rightclick on your current menubar and select Customize
2) Click New, and give it a name
3) Click Properties - Type, then select Menubar
4) Now fill your new Menubar ... you can use any of the builtin commands, so you can build one that looks almost exactly like the current menubar, except you leave out the commands you don't want people to use. You can drag/drop from current menubars, or use the Commands section in the dialog to add items.
5) Close and save this
6) Click Tools - Startup and uncheck Allow Full Menus, Allow BuiltIn Toolbars, and Allow Toolbar/Menu changes.
7) Still in Startup, select your new Menubar in the "Menu Bar" dropdown
This will force Access to use YOUR menubar for this application. This will travel with the database, so you don't need to set it for each instance of Access.
Business Accounts
Answer for Membership
by: peakpeakPosted on 2008-03-04 at 03:19:09ID: 21039902
You could use a Group Policy to prevent that particular user from running Excel but if he needs to use it for other purposes that's not an option.
http://www.windowsnetworki