By way of introduction, I'm a s/w engineer by trade, but _not_ a database programmer.
I'm currently putting to gether a small application for a mate. I've partitioned it into two Access databases, one as the application, the other to hold the data (this allows me to issue updates to the app without impacting the end-user's data).
The problem I have is is related to the fact that I now need to secure the database files in order to protect them from tampering and also to protect our Intellectual Property.
At startup, my AutoExec macro calls a function that creates links to the tables in the "data" file, using DoCmd.TransferDatabase(). This is necessary since the link contains the full path to the remote database, thus I cannot hard code these links as I have no control over the installation environment.
This has all been working fine until the point where I implemented security. If I log in as Admin, all is fine. However, if I log in as "user", a member of the Users group, I get:
Run-time error '3111':
Could not create; no modify design permission for table or query 'mytablename'
This occurs for the call to DoCmd.TransferDatabase(), where mytablename is the destination table name.
I have granted, to the Users group, Modify Design for new tables (and indeed for the few extant tables) in the app database.
Yet, I cannot shake off this error.
MSDN does not help: "In general, for users to access a linked table, grant Read Data and Read Design permissions on the table in the back-end database, and Modify Design permission on the table link defined in the front-end database so that users can conveniently re-link the table."
I've done all of that, but it does not appear to work.
Okay, things have moved on since I first tried this.
I've done away with linked tables and replaced them with queries of the form "SELECT * FROM <remote_table_name> IN <remote_db_path>"
However, an equivalent problem now manifests itself.
I have defined two users, say Bill and Ben. Both are members of the Admins group. The Admins group has full permissions on all objects.
Bill is the owner of each my queries. However, I need Ben to open the database and to be able to change the definition of the queries (since only he knows the path to the remote db). So he needs to set the .SQL property of the QueryDef object.
Despite the permission settings, Access keeps complaining "Run-time error 3033. You do not have the necessary permissions to use the 'query_name' object. Have your system administrator or the person who created this object establish the appropriate permission for you"
I get the same if I simply try to edit the query in Design View, when logged in as Ben.
Bill and Ben both have identical permission settings but only Ben fails in this way. Presumably this is because Bill is the owner of the queries.
However, how on Earth do I make Access allow users other than the objects' owner to modify them?