Unanswered: Access2007 privileges, can't create table, can create query
We're still on Access97, considering upgrading to Access2007. Something came up in a meeting the other day ...
Is there a feature in Access2007, which would prevent someone from creating tables, but would allow them to write queries & reports? We link in our data from Oracle, but users always end up needing to store data, & take the shortcut around IT to set up a mini-system in Access.
Our goal would be to continue allowing them to use Access as a BI tool for querying data, but not allow them to store any data locally within the Access database.
No. Userlevel security was removed in Access 2007. It supports it IF the file is in a file format of 2003 or earlier, but if the file is upgraded to 2007 file format, then all userlevel security permissions (aka privileges) are stripped away.
I personally would NOT recommend the upgrade if you need this level of security. Upgrade to 2003, but not 2007.
This was the suggestion as I understood it (note that I'm fabricating the *.MDT file extension just to get the concept across) ...
MSAccess2007 has the ability to split out a database into 2 files. The *.MDB would still hold all queries, forms, reports, VBA ... but when it comes to tables, it will only hold a table definition. Locally stored data will actually exist in another file, an *.MDT file. The consultant thought that we could prevent the creation of *.MDT files on the network, thus preventing people from storing data locally in an MSAccess database.
Sounds far-fetched, in that I've never heard of an option in Active Directory which allows you to prevent certain file types, but I thought it was worth asking if it was a possibility. I'm also not familiar enough with MSAccess2007 to know if what used to be just an *.MDB file in Access97, is now a multi-file aggregation in MSAccess2007.
The files just have different extensions, MDB is ACCDB in 2007 and MDE is ACCDE in 2007. Their functionality is identical. They are all access databases. Tables do not have an extension. If a user can open a 2007 Access database and modify forms/reports/code then NOTHING will stop them creating and populating tables in that database.
I think your consultant is trying way too hard to give you the "yes" answer.