Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    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.

    Thanks,
    Chuck

  2. #2
    Join Date
    Sep 2009
    Posts
    3
    Under Navigation Options you can uncheck the Object Type 'Tables' but this can easily be undone without any other actions.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Bear with me, as I have no experience with Access2007 ...

    I overheard a comment by a contractor that Access2007 can have several files associated with it, and one of those files could be used for storing the contents of locally defined tables.

    If we were able to configure the network to not allow these particular files, could we achieve our end-goal of preventing locally stored data?

    (BTW, I wasn't aware that user-level security in Access97 could prevent some types of objects from being created, but not others).

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What has the network got to do with LOCALLY stored tables?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    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.

    --=Chuck

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •