Access Front End with Excel Back End

[ryan]

I have built a database for our office that I have set user level security.
The source of the database is an excel workbook that we are supplied with
every week. Initially, I wanted to simply link to the table, so that when we
got the updated workbook, all we would have to do is write over it on the
network. However, since we want to secure the data, is there a way to tie it
to my access workgroup information file? In other words, if I log onto the
database, I can see the underlying tables. If I try to open the excel
document without logging into the database, can I make it prompt me for the
password in the same way access does?

 

[Ed Metcalfe]

I have built a database for our office that I have set user level security.
The source of the database is an excel workbook that we are supplied with
every week. Initially, I wanted to simply link to the table, so that when
we
got the updated workbook, all we would have to do is write over it on the
network. However, since we want to secure the data, is there a way to tie
it
to my access workgroup information file? In other words, if I log onto
the
database, I can see the underlying tables. If I try to open the excel
document without logging into the database, can I make it prompt me for
the
password in the same way access does?

No, I don't think so. Access user-level security is for securing Access
database files only.

I'm guessing, because I've never actually had to do it, but I'd imagine you
would have to secure the Excel file via the Excel application and then
figure out some VBA to unsecure the file at runtime, make the changes, and
the resecure it.

Adding a reference to the Excel object library to your Access database and
then perusing the Object Browser may point you in the right direction. If
not post back and I'll see what I can work out for you.

Ed Metcalfe.

 

[ryan]

Thanks Ed for your response. I wanted to clarify one thing. The excel data
contains sensitive data about wages, so I don't want unauthorized users to
access it. The database only organizes the data such that it is easier to
comprehend. There is no data updating. My workaround has been to import the
excel data into the database, and then secure the database on the network.
It would be easier to have linked tables in access because we could simply
overwrite the excel workbook every week. As it currently is, we have to
import 4 tables (one for each of the 4 worksheets in the workbook). I just
wondered if there was an easy way to protect linked sources since I'm not
very experienced at VB.......

 

[Tom van Stiphout]

On Sun, 27 Jan 2008 17:25:01 -0800, ryan

You may also want to ask this question in an Excel newsgroup.
No, you cannot use Workgroup Security to secure an Excel workbook. But
Excel has a few options, depending on how strong you want the security
to be. For example you can write a macro connected to the Open event,
and prompt for a username/pw. A worksheet can be hidden, and "very
hidden". I'm not sure what impact these options would have on your
ability to link to the workbook. A few minutes of experimentation
should bear that out.

-Tom.

 

[Keith Wilby]

The excel data
contains sensitive data about wages

The answer is painfully simple - don't use Excel - or Access for that
matter. There have been numerous security blunders in the UK recently where
such data has "gone missing" and it's exectly this kind of practice that's
the cause.

 

[Leslie Isaacs]

Ryan

I'm no expert (I usually ask questions on these newsgroups, not answer
them!), but I'm sure the simplest thing would be to import the spreadsheet
data into your secures database. I reckon that would take little longer than
the spreadsheet overwrite that you have described. The first time you do it
you would add the data to a new table: the 2nd and all other times you would
add the data to the exiting table (the one created the 1st time). If you do
it this way you would have the data in the database iteslf, and also - by
having a 'import_date' field in the import table, with default value date(),
you would be able to keep (and therefore report on, filtering by
'import_date') all the previously imported data.

So just do File>Get External Data>Import, and use the wizard.

Hope that helps.
Leslie Isaacs

 

[Tony Toews [MVP]]

The answer is painfully simple - don't use Excel - or Access for that
matter. There have been numerous security blunders in the UK recently where
such data has "gone missing" and it's exectly this kind of practice that's
the cause.

And the Excel file should, at the very least, be password protected
with a minimum of 10 or 12 character password. This assumes that that
version of Excel doesn't have cracking software available for it.

I do agree that the data should be imported into a SQL Server backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ed Metcalfe]

And the Excel file should, at the very least, be password protected
with a minimum of 10 or 12 character password. This assumes that that
version of Excel doesn't have cracking software available for it.

I do agree that the data should be imported into a SQL Server backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Tony,

Don't all versions of Excel have password-cracking software available for
them?

Ed Metcalfe.

 

[Tony Toews [MVP]]

Don't all versions of Excel have password-cracking software available for
them?

I have no idea. I would've thought that by now at least Excel 2007
used a good encryption system.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/