Got some VBA code behind a button.

User clicks a "Save" button, VBA code pushes contents of a
worksheet into an MS Access DB.

The catch is that there are many users - each with their own .XLS
and there's a requirement that one user cannot peek at another
user's info. i.e. Open up the MS Access DB directly and browse
the table we're pushing data into.

I didn't want to get into the MS Access security model, so that
means that the MS Access DB is password protected.

Right now, the PW is in a Const string in the VBA, which feeds it
to a DAO.OpenDatabase via the Connect string.


Seems like a chicken-egg dilemma, but it would be nice if the
code behind that "Save" button could get to the PW, but somehow a
sophisticated user bent on snooping could not.

Only thing I can think of is somehow making it impossible for a
regular user of the .XLS to see VBA code, step through VBA code,
and/or get to an Immediate window.

Suggestions?
--
PeteCresswell

Put your code in a VB6 ActiveX dll.

RBS


"(PeteCresswell)" wrote in message
...
Got some VBA code behind a button.

User clicks a "Save" button, VBA code pushes contents of a
worksheet into an MS Access DB.

The catch is that there are many users - each with their own .XLS
and there's a requirement that one user cannot peek at another
user's info. i.e. Open up the MS Access DB directly and browse
the table we're pushing data into.

I didn't want to get into the MS Access security model, so that
means that the MS Access DB is password protected.

Right now, the PW is in a Const string in the VBA, which feeds it
to a DAO.OpenDatabase via the Connect string.


Seems like a chicken-egg dilemma, but it would be nice if the
code behind that "Save" button could get to the PW, but somehow a
sophisticated user bent on snooping could not.

Only thing I can think of is somehow making it impossible for a
regular user of the .XLS to see VBA code, step through VBA code,
and/or get to an Immediate window.

Suggestions?
--
PeteCresswell

Hi Pete.

To hide your code and deny access to
the VBE, try:

In the VBE,

Tools | Properties | Protection |
Check 'Lock project for viewing | add a
password | Save and close the file

Note, however, that this protection is
largely illusory: it can readily be
circumvented by a knowledgeable user
or, indeed, by anyone able and willing
to Google.


---
Regards,
Norman


"(PeteCresswell)" wrote in message
...
Got some VBA code behind a button.

User clicks a "Save" button, VBA code pushes contents of a
worksheet into an MS Access DB.

The catch is that there are many users - each with their own .XLS
and there's a requirement that one user cannot peek at another
user's info. i.e. Open up the MS Access DB directly and browse
the table we're pushing data into.

I didn't want to get into the MS Access security model, so that
means that the MS Access DB is password protected.

Right now, the PW is in a Const string in the VBA, which feeds it
to a DAO.OpenDatabase via the Connect string.


Seems like a chicken-egg dilemma, but it would be nice if the
code behind that "Save" button could get to the PW, but somehow a
sophisticated user bent on snooping could not.

Only thing I can think of is somehow making it impossible for a
regular user of the .XLS to see VBA code, step through VBA code,
and/or get to an Immediate window.

Suggestions?
--
PeteCresswell

On Jul 15, 6:51 pm, "RB Smissaert"
wrote:
Put your code in a VB6 ActiveX dll.

I don't have much experience with .DLLs - maybe I've created one or
two in the distant past - but it seems like the user could still open
up an Immediate window, find the code that calls the routine in
the .DLL, execute same in the Immdiate window, and see the result.

Have I got it right?

If so, it sounds like I'm fighting Mother Nature on this one....

But the dll doesn't have to expose the password.
It could make the connection get the data and pass
it to Excel.

RBS

"PeteCresswell" wrote in message
...
On Jul 15, 6:51 pm, "RB Smissaert"
wrote:
Put your code in a VB6 ActiveX dll.

I don't have much experience with .DLLs - maybe I've created one or
two in the distant past - but it seems like the user could still open
up an Immediate window, find the code that calls the routine in
the .DLL, execute same in the Immdiate window, and see the result.

Have I got it right?

If so, it sounds like I'm fighting Mother Nature on this one....

Per RB Smissaert:
But the dll doesn't have to expose the password.
It could make the connection get the data and pass
it to Excel.

Yesssssss!!!!!

Takes awhile to soak in sometimes....

Thanks.
--
PeteCresswell