Access 2007 Security

[Jeff Byrd]

I understand that Microsoft has eliminated user level security in Access
2007. I have a database that I have some data in that I don't want every
user to be able to change and some that I don't want everyone to have access
to. It sounds like I can no longer do this if I update the database front
end and back end to 2007. Is that right. If it is, isn't that a major step
backwards?

Are there any good sites out there yet that deal with Access 2007?

 

['69 Camaro]

Hi, Jeff.

I understand that Microsoft has eliminated user level security in Access 2007.

Not true. Access 2007 supports User-Level Security from previous versions as
long as the file format remains as a Jet file (MDB). Once the file format is
converted to ACE format (ACCDB), User-Level Security is not available. However,
it's a lot easier to manage User-Level Security in Access 2003 and earlier than
in Access 2007 due to the UI Ribbon "missing" stuff that was formerly on the
built-in menus. If you want to keep managing User-Level Security in an existing
database, keep your database in MDB format and keep an earlier version of Access
installed on at least one computer.

I have a database that I have some data in that I don't want every user to be
able to change and some that I don't want everyone to have access to.

If the data needs to be secure, don't store it in Access. Store the data in a
client/server database, such as Oracle or SQL Server.

It sounds like I can no longer do this if I update the database front end and
back end to 2007. Is that right. If it is, isn't that a major step
backwards?

You don't have to convert the file to ACCDB format. It can remain in MDB format
and still be opened in Access 2007, and User-Level Security will still work.

Are there any good sites out there yet that deal with Access 2007?

Only a couple so far:

http://blogs.msdn.com/access/

http://www.AccessJunkie.com

If you have a few hundred hours or a few thousand hours of spare time, feel free
to build your own Web site to offer advice to others about Access 2007.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[jeff]

Has Microsoft given any reason for not making user level security avalalble
in the ACE format? It just doesn't make sense to not offer a form of
security availble in a previous version.

Hi, Jeff.

I understand that Microsoft has eliminated user level security in Access
2007.

Not true. Access 2007 supports User-Level Security from previous versions
as long as the file format remains as a Jet file (MDB). Once the file
format is converted to ACE format (ACCDB), User-Level Security is not
available. However, it's a lot easier to manage User-Level Security in
Access 2003 and earlier than in Access 2007 due to the UI Ribbon "missing"
stuff that was formerly on the built-in menus. If you want to keep
managing User-Level Security in an existing database, keep your database
in MDB format and keep an earlier version of Access installed on at least
one computer.

I have a database that I have some data in that I don't want every user
to be able to change and some that I don't want everyone to have access
to.

If the data needs to be secure, don't store it in Access. Store the data
in a client/server database, such as Oracle or SQL Server.

It sounds like I can no longer do this if I update the database front end
and back end to 2007. Is that right. If it is, isn't that a major step
backwards?

You don't have to convert the file to ACCDB format. It can remain in MDB
format and still be opened in Access 2007, and User-Level Security will
still work.

Are there any good sites out there yet that deal with Access 2007?

Only a couple so far:

http://blogs.msdn.com/access/

http://www.AccessJunkie.com

If you have a few hundred hours or a few thousand hours of spare time,
feel free to build your own Web site to offer advice to others about
Access 2007.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Jeff Conrad]

in message:

Not true. Access 2007 supports User-Level Security from previous versions as long as the file
format remains as a Jet file (MDB). Once the file format is converted to ACE format (ACCDB),
User-Level Security is not available. However, it's a lot easier to manage User-Level Security in
Access 2003 and earlier than in Access 2007 due to the UI Ribbon "missing" stuff that was formerly
on the built-in menus.

If you keep the file in MDB format, I believe all the security menus appear on a special Ribbon
group. If you convert up to ACCDB format that group simply disappears off the Ribbon.

http://www.AccessJunkie.com

Thanks Gunny.

P.S. Good to have you back; you've been sorely missed.

 

['69 Camaro]

Hi, Jeff.

If you keep the file in MDB format, I believe all the security menus appear on
a special Ribbon group.

Ah. Thanks. I'm blind, 'cause I missed it. (Or was the special ribbon group
not in Beta 2? I don't have an MSDN subscription to download Access 2007 so I
have to wait for the retail version to become available at the end of next
month.)

P.S. Good to have you back; you've been sorely missed.

Thanks. Resistance was futile. I'm back in the Collective. ;-)

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi, Jeff.

Has Microsoft given any reason for not making user level security avalalble in
the ACE format?

Probably, but I don't know Microsoft's official stance on that.

It just doesn't make sense to not offer a form of security availble in a
previous version.

It appears to me that Access has been pointed in a new direction, Sharepoint
Services. Most things that disappeared are virtually obsolete when Sharepoint
Services is available, such as replication.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Has Microsoft given any reason for not making user level security avalalble in
the ACE format? It just doesn't make sense to not offer a form of security
availble in a previous version.

 

[Douglas J. Steele]

At http://blogs.msdn.com/access/archive/2005/10/19/482845.aspx Eric implies
that security should never have been built into previous versions of Access:
"Since JET is a file based database system where users need physical access
to the file to operate on their data, the concept of user level security in
Jet to assign different levels of user access to the data within the same
file was not recommended. To have multiple people use the database but with
different data access privileges, the recommended practice was to move this
data to a centralized service like SQL server or SharePoint lists. However,
Jet has had this feature for some time and it has worked OK for usability
and custom navigation scenarios but isn’t recommended for actual security."


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Has Microsoft given any reason for not making user level security
avalalble in the ACE format? It just doesn't make sense to not offer a
form of security availble in a previous version.

 

[Joan Wild]

If you keep the mdb format, you can still use Access ULS. It's not there in
the accdb format. If you don't/can't move to SQL Server for real security,
but want some control, you can roll your own.
Some examples
http://groups.google.ca/group/micro...ea71aeab394?lnk=st&q=&rnum=1#a9856ea71aeab394
and
http://www.utteraccess.com/forums/s...=373275&page=1&view=collapsed&sb=5&o=&fpart=1

 

[Jeff Conrad]

in message:

Hi Gunny,

Ah. Thanks. I'm blind, 'cause I missed it. (Or was the special ribbon group not in Beta 2? I
don't have an MSDN subscription to download Access 2007 so I have to wait for the retail version
to become available at the end of next month.)

I don't have Beta 2 loaded any more, but I'm 99% sure it was there in Beta 2 as well. If you open an
MDB/MDE file you can see an Administer group on the Database Tools tab. On that group are two
buttons-Users and Permissions and Replication Options. If you click these buttons you'll see the
standard menu choices. If you work on an ACCDB/ACCDE file, however, that whole group just goes
bye-bye.

Thanks. Resistance was futile. I'm back in the Collective. ;-)

The Collective wouldn't be the same without you!
;-)

 

[Neil Jordan]

That is all very well from the data point of view, but when you consider
Access databases that are written for even a relatively small company, I
often have to block users from other objects in the database - Forms,
Reports etc.

Microsoft's new ACCDB format basically means that I would have to port all
my data over to another source like SQL, and have to embed lots of code to
prevent access to the other objects. Basically I would have to write my own
security system using tables of names and groups as well as security, then
try to get hold of the domain login name.

Although I have seen advances in the new version, it just looks like what
other people are commenting on, that they are trying to dumb down the whole
program - perhaps they want to generate more sales to normal users from it.
From my point of view, as someone who develops in Access, I am concerned
that at some point the MDB format will be abandoned and a huge chunk of
current development work out there will become impossible to upgrade.

Neil

 

[David W. Fenton]

It appears to me that Access has been pointed in a new direction,
Sharepoint Services. Most things that disappeared are virtually
obsolete when Sharepoint Services is available, such as
replication.

That's malarkey.

Jet Replication does much, much more than Sharepoint does. And Eric
Rucker admitted on the Access blog that the Sharepoint integration
was not even close to a replacement for replication.

 

[David W. Fenton]

Although I have seen advances in the new version, it just looks
like what other people are commenting on, that they are trying to
dumb down the whole program - perhaps they want to generate more
sales to normal users from it.

If they *do* sell it to a lot of "dumb people," it means a lot of
new work for us developers, cleaning up the messes the "dumb people"
make.

I'm all for improvements to Access that make it more usable by
non-developers. What I'm against is the removal of key functionality
to accomodate those newbies.

 

['69 Camaro]

Hi, David.

That's malarkey.

Jet Replication does much, much more than Sharepoint does.

You're absolutely right, but one of Sharepoint Services' selling points is that
data can be stored and updated from distributed office locations in the
enterprise, something that Jet replication has been used for. Jet replication
can't provide real time updates, but Sharepoint Services can, so from these
companies' viewpoints, migrating to Sharepoint Services made Jet replication
obsolete.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Joan Wild]

You're absolutely right, but one of Sharepoint Services' selling
points is that data can be stored and updated from distributed office
locations in the enterprise, something that Jet replication has been
used for. Jet replication can't provide real time updates, but
Sharepoint Services can, so from these companies' viewpoints,
migrating to Sharepoint Services made Jet replication obsolete.

I'm more concerned with the small businesses than enterprises. Replication
is gone, and even Terminal Services can't be used unless you install the
Enterprise edition of 2007. http://support.microsoft.com/kb/924622

 

['69 Camaro]

Hi, Joan.

even Terminal Services can't be used unless you install the Enterprise edition
of 2007. http://support.microsoft.com/kb/924622

Geez! The list of reasons _not_ to upgrade to Access 2007 just keeps getting
longer and longer . . . .

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[tina]

well, before i even heard any of any other issues, here was the big sticking
point for me - that A2007 won't run on Win2KPro. i had planned on buying A07
when it goes on sale...but i'm not about to buy a new OS just to use the new
version of Access!

Hi, Joan.

even Terminal Services can't be used unless you install the Enterprise edition
of 2007. http://support.microsoft.com/kb/924622

Geez! The list of reasons _not_ to upgrade to Access 2007 just keeps getting
longer and longer . . . .

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[David W. Fenton]

Hi, David.


You're absolutely right, but one of Sharepoint Services' selling
points is that data can be stored and updated from distributed
office locations in the enterprise, something that Jet replication
has been used for. Jet replication can't provide real time
updates, but Sharepoint Services can, so from these companies'
viewpoints, migrating to Sharepoint Services made Jet replication
obsolete.

I don't think anybody is using Jet Replication for that purpose any
more. If you're using Jet for your back end, then you're running
your app on Windows Terminal Server in that environment, and then
Sharepoint is just completely irrelevant to the question of sharing
data.

The only valid application for Jet Replication today is for
disconnected users who need to edit data and then return to the
"mother ship" occasionally. I don't know that Sharepoint supports
that situation at all.

And the lack of a real database engine in Sharepoint makes it
completely unacceptable for storing any real data (i.e., no
referential integrity, no real primary keys, etc., etc.).

 

[David W. Fenton]

I'm more concerned with the small businesses than enterprises.
Replication is gone, and even Terminal Services can't be used
unless you install the Enterprise edition of 2007.
http://support.microsoft.com/kb/924622

Replication is *not* gone!!!! It's just like user-level security,
still fully supported in MDBs, and not supported in the new data
format.

 

[Joan Wild]

I was referring to the new format.

 

['69 Camaro]

Hi, Tina.

A2007 won't run on Win2KPro.

I had planned on testing Access 2007 Beta on my Windows 2K workstation earlier
this year, but discovered the same thing you did. That combo won't work. You
have a work-around that won't cost you anything, but it involves two big "ifs."
_If_ you signed yourself up for the Vista Beta 2 earlier this year and
downloaded it, you can run the free Virtual PC 2004 from Windows 2K and install
Vista Beta 2. (Doesn't it have the technical refresh by now, too?) Then
install Access 2007 (retail, trial, or beta edition) on the Vista OS on Virtual
PC -- if your workstation has enough memory and power under the hood.

Please see the following Web page for the free Virtual PC 2004 download:

http://www.microsoft.com/downloads/...9D-DFA8-40BF-AFAF-20BCB7F01CD1&displaylang=en

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

well, before i even heard any of any other issues, here was the big sticking
point for me - that A2007 won't run on Win2KPro. i had planned on buying A07
when it goes on sale...but i'm not about to buy a new OS just to use the new
version of Access!