Password in Query

[Doug]

Hi,

I need to run a query on an external table in an Access
database that is password protected. Can I do that by
using the IN statement? I can get it to work if I remove
the password protection from the database with this
command:

SELECT * FROM tblUser IN 'c:\f2000\FalconSec.mdb'

However, I need to have this database password protected,
so how do I specify the password?

Thanks,

Doug

 

[Bill Crawford]

Hi:

Quoted from MS Access Help:

start quote

To link a table from a Microsoft Access database that is password-protected,
you must supply the correct password. If you supply the correct password,
Microsoft Access stores the database password with the information that
defines the link to the table. After the link has been defined, any user who
can open the database that the table is linked to can open the linked table.
When a user opens the linked table, Microsoft Access uses the stored
password to open the database where the table is stored. If the password is
changed for the database where the table is stored, the next time the linked
table is opened, the new password must be supplied before Microsoft Access
will open it.

Microsoft Access stores the database password in an unencrypted form. If
this will compromise the security of the password-protected database, you
should not use a database password to protect the database. Instead, you
should define user-level security to control access to sensitive data in
that database.

end quote

 

[Doug]

Thanks Bill,

I did use this method to access the table, but I don't
really want to link the table because I don't want people
to have access to the information in the table. That's
why I have the database password protected. I just want
to run a report on the table using a query. There are
two properties in a query named 'Source Database'
and 'Source Connect Str'. Source Database can be used to
specify an external database to use for the query. So I
put in c:\f2000\falconsec.mdb in the Source Database
field. This works fine if the database isn't password
protected. But once I add the password protection, I get
an invalid password error. How can I specify the
password?

Doug

 

[Bill Crawford]

HI:

A field asking for the password doesn't popup? Weird

 

[Guest]

Here's the scenario:

I have a Query defined: qryUsers
This query has as it's source database: c:\f2000
\falconsec.mdb
falconsec.mdb is password protected.
If I double click on qryUsers, it says "Not a valid
password"
If I right click and select "Design View" I get a
Password Required box.
I then enter the password and have the design view for
the query.
If I right click and select "Datasheet View" I get "Not a
valid password"

So, a password prompt doesn't popup when I actually go to
access the data. There must be some way to enter the
password in a property, right? Or don't they want you
doing this since it wouldn't be encrypted and then people
can easily find out what the password is?

Doug

 

[Doug]

Just in case anybody is following this, I figured out how
to do it. In the Source Connect Str property of the
Query, I input the following:

MS Access;PWD=Whatever;DATABASE=C:\F2000\FalconSec.mdb

and leave the Source Database property blank. Now I
don't have to link the tables contained in this database.

Doug

 

[Van T. Dinh]

Yes but are you aware that linked Tables is the most efficient way from the
processing point of view?

 

[Doug]

Hi Vin,

I was aware that it is more efficient. There are a
couple of factors in my not wanting or needing to do
that. First, I am accessing three tables that don't
contain too much data (<50 records), so I didn't think
the performance hit would be too bad. And second, the
information in the external database is user and password
and security level information, and I didn't want that
information visible. And it's my understanding that if I
link the tables then their information will be visible.
Is my thinking correct?

Doug

 

[Van T. Dinh]

For small Tables, I guess the performance hit would be negligible.

My users don't see the Database Contaniers window so they can't see the
Tables (linked or not) anyway.