Adding security to Access applications AFTER the event?

[DavidGeorge]

I have made a fundamental error in design and ignored security until
the end of the project. This is largely because the whole process was
a steep learning curve (helped enormously by this group) and I didn't
know when I started (and still don't really understand), how to
implement security.

I have an application in two parts:
- the first part is used by a number of people on standalone laptops
who collect information about patients. Data is exported to Excel
files and emailed to me.
- the second part (which only I have access to) imports data from the
Excel files and produces a number of complex reports.

My problems are:
- Can I now secure the first part of the program so that the data
cannot be accessed except through the program?
- Is there a simple way of encrypting the Excel files for transmission?
It would be tedious but not be too difficult for anyone to write their
own report generating program thereby removing me from the equation.
Also the data is confidential.
- Is there anything available to produce an .exe file from the Access
application?

[strvariant]

"DavidGeorge" <da*********@hotmail.com> wrote in
news:11**********************@l41g2000cwc.googlegr oups.com:

I have made a fundamental error in design and ignored security until
the end of the project. This is largely because the whole process was
a steep learning curve (helped enormously by this group) and I didn't
know when I started (and still don't really understand), how to
implement security.

I have an application in two parts:
- the first part is used by a number of people on standalone laptops
who collect information about patients. Data is exported to Excel
files and emailed to me.
- the second part (which only I have access to) imports data from the
Excel files and produces a number of complex reports.

My problems are:
- Can I now secure the first part of the program so that the data
cannot be accessed except through the program?
- Is there a simple way of encrypting the Excel files for transmission?
It would be tedious but not be too difficult for anyone to write their
own report generating program thereby removing me from the equation.
Also the data is confidential.
- Is there anything available to produce an .exe file from the Access
application?

Hi David,

As for the .exe route you would not be able to make an executable file
from Access.

You could add some code to Access that would run after the data is
exported to Excel:

1) Open a hidden instance of the Excel workbook

2) Hide the sheet that contains the data via VBA

3) Password protect the workbook, also via VBA

4) Save all changes and close the instance of Excel.

The code for this would reside within the Access application. To protect
the code in Access you would generate an .mde file instead of the usual
..mdb file. This compiles your code and locks it down to prevent viewing
and design changes.

In order to do this I would expect that a 'Version 2' would need to be
sent out to the users.

These are just some suggestions --- as you know there are many different
ways to do the same thing.

Hope this helps you,

strvariant

[Br]

In news:11**********************@l41g2000cwc.googlegr oups.com,
DavidGeorge <da*********@hotmail.com> said:

I have made a fundamental error in design and ignored security until
the end of the project. This is largely because the whole process was
a steep learning curve (helped enormously by this group) and I didn't
know when I started (and still don't really understand), how to
implement security.

I have an application in two parts:
- the first part is used by a number of people on standalone laptops
who collect information about patients. Data is exported to Excel
files and emailed to me.
- the second part (which only I have access to) imports data from the
Excel files and produces a number of complex reports.

My problems are:
- Can I now secure the first part of the program so that the data
cannot be accessed except through the program?
Mostly you can only make it harder, not impossible.

I rename the backend data files from MDB to something else. I wrote a
system that uses MDB data files (renamed) in a way similar to how a Word
document would be loaded in Word as that is how the client wanted this
application to work.

The MDB also has workgroup or password security. You could write a
routine to ZIP the data and email it to you....
- Is there a simple way of encrypting the Excel files for
transmission?
They'd still have access to the Excel file wouldn't they before it is
encrypted/emailed?

Ypud have to encrypt the data before exporting it. Then it wouldn't
matter what format you exported it to.
It would be tedious but not be too difficult for anyone
to write their own report generating program thereby removing me from
the equation. Also the data is confidential.

- Is there anything available to produce an .exe file from the Access
application?

Nope. Closest you can get to a "compiled" version is an MDE which still
requires Access.

Br@dley

[DavidGeorge]

Thanks strvariant and Br@dley for your ideas ... they're a great help.

I guess I'm not trying to keep the CIA out of the data; just tinkerers.
So far, I've split the database and password protected the BE and made
the FE into an MDE file. I've also tried putting an innocously-named
file somewhere on the computer and if it doesn't exist the program
exits instantly. I will be supplying the software to employees on
notebook computers so I can make sure the file exists ... if they copy
the application it seems unlikely that they would also copy the INF
(innocuously named file).

strvariant, you mentioned password protecting the workbook via VBA.
Could you give me any pointers as to how I might do this?

Br@dley, you mentioned a routine to ZIP the Excel file. If this could
be done from within VBA, immediately after the files were created then
I could delete the Excel files immediately after creation in one
seamless operation; but how? Any ideas?

In another thread someone mentioned disabling the shift key so that the
startup routine couldn't be bypassed. Does anyone know how this might
be done?

Many thanks to everyone for your help to date. It's been excellent.
I first dabbled with Access about 10 years ago but in those days,
unless you knew a friendly guru to mentor you through the process you
were stuck. It's amazing the level of help that's available nowadays
for (almost) every problem!

Regards DavidGeorge

[Douglas J. Steele]

To disable the shift key, you need to create a boolean database property
named AllowBypassKey and set its value to False. Take a look at
http://www.mvps.org/access/general/gen0040.htm at "The Access Web" for the
correct way to create this property (the example in the help file is
incomplete)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"DavidGeorge" <da*********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

In another thread someone mentioned disabling the shift key so that the
startup routine couldn't be bypassed. Does anyone know how this might
be done?

[Br]

In news:11*********************@f14g2000cwb.googlegro ups.com,
DavidGeorge <da*********@hotmail.com> said:

Thanks strvariant and Br@dley for your ideas ... they're a great help.

I guess I'm not trying to keep the CIA out of the data; just
tinkerers. So far, I've split the database and password protected the
BE and made the FE into an MDE file. I've also tried putting an
innocously-named file somewhere on the computer and if it doesn't
exist the program exits instantly. I will be supplying the software
to employees on notebook computers so I can make sure the file exists
... if they copy the application it seems unlikely that they would
also copy the INF (innocuously named file).
You can do things like renaming your files from MDB to something
else....
strvariant, you mentioned password protecting the workbook via VBA.
Could you give me any pointers as to how I might do this?
In the Tools menu of the VBA module screen... and then <your db name>
Properties.
Br@dley, you mentioned a routine to ZIP the Excel file. If this
could be done from within VBA, immediately after the files were
created then I could delete the Excel files immediately after
creation in one seamless operation; but how? Any ideas?
You'd need the ZIP DLLS and examples of how to call them. (Try a search
on Google)
In another thread someone mentioned disabling the shift key so that
the startup routine couldn't be bypassed. Does anyone know how this
might be done?

Check the database startup properties (Tools menu).

Br@dley