In my workbooks I often use one sheet to contain multiple database
queries from separate tables for lists of Department Id's, Account
Numbers, Project Id's, etc. I then assign names to the ranges and then
use these named ranges in validated cells. For each of the queries I set
the data range properties to refresh data on file open.

My problem is that I need to have the user who opens the file enter
their name and password so the lists are validated using the database
security to display only the Id's they are allowed to view and use. I
can set the data range properties to not save the password, however, if
there are multiple queries they are forced to re-enter their name and
password once for each query.

Using VBA, can I request a username and password, with the password
displayed as *** as they type it in, then apply this into each query as
I refresh them using VBA rather than refreshing the data on file open?

I would set this procedure to run in my Workbook_open() sub



Simon Shaw, CA
Parnter
AccountTech
www.accounttech.ca

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Couldn't you carry the uid and pwd as a variable and then
pass it each time in the connection string?

This would be similar to holding it as a session variable
on a web site.
-----Original Message-----
In my workbooks I often use one sheet to contain multiple
database
queries from separate tables for lists of Department
Id's, Account
Numbers, Project Id's, etc. I then assign names to the
ranges and then
use these named ranges in validated cells. For each of
the queries I set
the data range properties to refresh data on file open.

My problem is that I need to have the user who opens the
file enter
their name and password so the lists are validated using
the database
security to display only the Id's they are allowed to
view and use. I
can set the data range properties to not save the
password, however, if
there are multiple queries they are forced to re-enter
their name and
password once for each query.

Using VBA, can I request a username and password, with
the password
displayed as *** as they type it in, then apply this into
each query as
I refresh them using VBA rather than refreshing the data
on file open?

I would set this procedure to run in my Workbook_open()
sub



Simon Shaw, CA
Parnter
AccountTech
www.accounttech.ca

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!
.

Hi Simon,

In VBA project, we can add a userform and two textboxs and a button onto
the userform.
TextBox1: UserName
TextBox2: Password,we can set the textbox's PasswordChar property to "*",
so that when user inputed, the character will be *****.

And in the Button click event, we can get the Textboxs' values and build it
into the connectionstring to do the query. We can popup the userform1
whenever we wants user to input username and password by call the
userform1.show.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Simon,

Have you tried my suggestion?
If you still have any concern on this issue, please feel free to post here
and I will work on it with you.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.