How can i secure the database my VB is using?

hiya,

I have a program, that uses to access databases, one of which has confidential information that not everyone using the program is allowed to see. The databases have to be in the same directory as the exe for the program to work.

What is the best way to make the users unable to open the databases using access? i.e. i only want them to be able to get to them through my program.

Any ideas?
Thanks for your time
Ang

#1) Security by ignorance. You could use a startup form or macro that executes a DoCmd.Quit statement - this would cause the database to close as soon as it was opened. A startup form or macro in an Access database can be avoided if you hold down SHIFT while opening the database - so this type of 'security' is easily thwarted by knowledeable users. Another approach might be to make the file 'hidden' in the file system - but this is also easily thwarted by mildly knowledeable users.

#2) You could add Access security to your database and lock everyone out that way. Access security can be a pain to work with but it can be done. If you use Access security you'll need a special connection setup to get into your database from VB code - a normal connection string alone isn't enough.

Good luck,
Paul

damn. well there's some clever un's here so i guess i'll have to go with option 2....

how do i do the special connection setup? and how do i do the access security? can i just put a password on opening the database?

Thanks again :)
Ang

Unfortunately, properly securing an Access database is a little more complicated than just attatching a password to it - I'm not even sure if that's possible. It's way beyond the scope of this post to explain it all - because it's a lot of info and because it's been a while since I've done it (I use SQL Server now). I would suggest looking around (searching this forum, MSDN, or MS) for tutorials on 'Access Security'. I believe there is extensive documentation for it buried on the Office97 CDROM (probably depends on what version you have).

Somethings that are involved:

You have to create a .mdw file that will contain all the usernames and groups and their security info for your database. Once properly secured your database can only be opened with a shortcut that links your database and this workgroup file together - and they must have a username and password (unless you make up users without passwords, etc).

Here's the connection setup you'd need to use with ADO - although you have a lot of work cut out for you to properly secure your database using Access security before you get to this!!!

Code:

---

conYourConnectionObject.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51"
conYourConnectionObject.Properties("Jet OLEDB:System database") = "c:\pathto\your.mdw"
conYourConnectionObject.Properties("User ID") = "YourUsername"
conYourConnectionObject.Properties("Password") = "YourPassword"
conYourConnectionObject.Open "Data Source=c:\pathto\your.mdb"

---

The trick with the above is that you have to let ADO know where the .mdw file is and what the username and password that you want to connect with is...you can't just use the username and password fields of a typical one-line connection string because there's nowhere in that one-line to indicate the location of the .mdw.

I have secured Access databases (but it's been a while) and worked with them via ADO using a setup like this - so I know it all works. The biggest challenge of all this is getting the Access security setup and working correctly.

The best advice I can give you before you indulge in this is to make some backup copies of your database before you (attempt) to secure it. It might take a few tries to get everything right.

Good luck,
Paul

Gruesome........... ah well, that should keep me busy next week

Thanks heaps Paul :)
Ang

Look. If you simply want to use a DB password through your program to open the database, you can use this (with DAO)

Set DB = DAO.OpenDatabase(App.Path & "\MyDB.mdb", False, False, ";PWD=thePassword")

Then your program can limit what the user sees.
You'd need to establish the password with Access first. It's done through one of the menu options. See the help on password for access.

really? choice, i've set the password in access b4, sweet as. but then VB couldnt open it either

will that be secure enough? is their any way to bypass as password in access?

you guys rock :)
Ang

ooh damn, it helps to read properly, i cant do it that way cause my program uses ADO, sorry, guess i should have said that before

Here's the ADO way
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:Database Password=MyDbPassword;"

I don't know about bypass the password. Maybe you have to be clever. Guess the password, that might be a problem. Anyway mdw files are supposedly more secure. It'll take you lots of time to figure out.

Jet OLED:DBatabase Password=MyDbPassword;" ya reckon?
mua ha ha, that works great, thanks heaps :)

Ang

It's the typing! I meant
"Jet OLEDB:Database Password=MyDbPassword;"

It's that damn character
"Jet OLEDB (colon) Database Password=MyDbPassword;"

mua ha ha, i know, it's cause : and D = :D, but i figured that's what had happened so i changed it and, like i said it works perfect.

dont panic :)
Ang

YOU CAN DO IT!

er........

Hi,

You can use the start up form AND disable the shift key so only those with the knowledge could unlock it... Then the start up form is safe to use as only developers will have a go at it.

I've used it before, seems fine, and great when your popup form states its deleting files/data (it ain't of course but you should see some worried looks from ppl if they have going into it LOL).

Anyways, thought you might wanna know

I have the connection string n code for ADO connection to Protected workgroup Access files (gasp) I can paste when I get home if you are interested.

Regards

Vince

heh, get my program to tell the boss it's deleting all his cofidential files eh?, mua ha ha, he'd love that. The password way works ok tho, so i think i'll just leave it as that, thanks all the same :)


Ang