Access password and Excel database query

[BrockLanders]

Hi, I'd like to add some very simple security to my Access 2000 database and would like to know the best approach.

I want to just add a database password so that everytime the database is opened, the user is prompted for a password. This database is also the source for a few Excel files using database queries as well. However, I do not want users to be prompted for a password when they open the Excel files.

Any ideas would be much appreciated.

Thanks

 

[Yarcadian]

Hello,

If all you want to do is prompt for a password without using the workgroup admin security feature, then try this.

Goto TOOLS -- SECURITY -- SET DATABASE PASSWORD

The problem with this approach is anyone who knows the password can open the database. So if you don't want Sally to be able to open it....all you can do is hope she does not learn the password.

Setting up groups and users is more time consuming and trickier, but considerably more secure. Hope this helps.

David

 

[BrockLanders]

Thanks for the reply.

I know how to setup the initial database password, but when a user tries to open an Excel file that's using a database query from the said database, they're prompted for a password as well. I'd just like the Excel file to open without using a password.

Using this approach, users can view the information in my database through Excel, but not have the privileges to edit any data.

Any thoughts?

Thanks

 

[Yarcadian]

Try going to DATA--IMPORT EXTERNAL DATA--DATA RANGE PROPERTIES and checking the box to save the password. I just did this and when I refreshed the data it prompted me for a user name and pw....I keyed it in, closed Excel and when I opened it up again, it did not prompt me for it again when I went to refresh the data.

 

[KornGeek]

I'm not very familiar with Excel, but there might be a way to set up the connection to the database and specify the password.

Another approach would be to export the data from the database, but then Excel would not be dealing with "live" data, and could potentially be out of date.

One final consideration, as far as security goes the Access password is NOT secure. There are utilities freely available that will display the password for you. If you really only need to discourage casual users, this password may be enough. However, anyone with a little bit of determination can get past it easily enough.

 

[batteam]

Let me tell you about my setup.

I have an Excel file, that when opened, uses macros behind the worksheet (On Open) to run queries from an Access database. Now this database is password controlled, but not using Access' built in Password security controls. What I did is to have a database 'on open' pop-up form that requires all db users to enter a user name AND their password. I say 'their' password because I have a little table made up of user names and the password of their own choosing. The code behind the start up form then simply checks the table to ensure the user name and entered password are correct, then allows db entry. Nice thing about this set up is, when the Excel file I mentioned earlier is opened and queries are run against the db, no password is asked for, so none is required by the Excel users. Now, anyone can open the Excel file but they don't have to know anything about Access or any password for the underlying Access db. And your db is password protected. Of course, you will have to secure the table of passwords, but that would go along with the same method you use to protect your tables from causal snoops, hackers, etc.

Hope these ideas help. Good luck.

 

[BrockLanders]

Thanks for all the feedback. The security for this database does not need be high level at all. I think I'll just create my own form prompting the user for a password when the db is opened as suggested by batteam

Thanks