Access security and permissions

[matttan]

Hi everyone,

Got one of those problems that should be straight forward but for some reason the answer is avoiding me like the plague.

Firstly, details of the area that I'm having problems with. I have a database. The core data is coming from two linked spreadsheets - identical fields, different data. I have set up a module and macro so that users can 'update data' on a button click. The update looks at the two linked tables in a query (call them 'clean queries'), which gets rid of a bit of mess in the source data (I needed to put a handful of rows at the top of the spreadsheet to make sure numbers link as numbers, etc). It then runs a 'make table query' and sets up a new table on the first data set. After this it runs an append query and appends the second data set to the new table. As the button is clicked subsequently the 'make table' query replaces the existing combined table with a new one.

Now, to the security problem. I want my users to be able to run this sequence, but cannot get it to. I've set up a user group for them, and have tried all sorts of different combinations of permissions on the relevant tables and queries/all tables and queries, and new tables and queries. I've tried giving all permission possible except administer. I've also changed the Run Permissions in all related queries to be "Owners", and have made sure that I am the owner and that my permissions cover everything.

Still I get an error message. The error has changed depending on how it is set, but at the moment it is "Could not create; no modify design permission for table or query 'tblCombinedData'".

The table it refers to is the new one that gets created each time the button is pressed. however under permissions, the user group does have permission to modify design for new tables. I've even tried it with 'adminster' permission on tables, and it won't work.

Is there anybody out there that can think of a solution for this?

Thanks so much if you can.

 

[tails]

when you log on under administrator or what ever the administrator's username and password is does it allow the table to be created?

As for the access problem have you tryed giving the users access to the complete drive of area that it is situated in. eg the e drive or the q: etc etc. That could be the cause of it. becuase if a user has access to a certain folder but not the entire drive itself then the permissions can get 'confused' with each other denying users certain access to everything that is under the drive.

 

[matttan]

Yep, when I log on with my user name (i have complete access to everything), then it will run the update.

All users will have access to the drive its on. At the moment, I'm the only one using it (in testing phase), and am just logging into the database as a different user to see what happens.

Thanks for such a quick start!

 

[tails]

Well while your testing create a new user, and give them complete access to the drive itself. (Not administrator rights) but as much access as you can give them with out allowing them to have admin rights. They will need all access to the drive which includes modify, create, delete, read & write/read & Execute full control. everything like that. but they need to done to the actual drive itself not the access table or the access folder that the access file is in. Just try that and see if it works.

 

[matttan]

Nope, still doesn't work.

I had a thought of a possible solution, but have never done anything like this, so some pointers would help.

Whatif I set up a front end database that has security by user groups, etc, and holds my analysis queries and reports in it, and a back end database that is simply a password protected database (no user groups, etc), which is the feeder for the front end queries. The thing I would need is for the 'update data' to be activated at the front end, but then it would activate a backend query, which isn't user group dependent.

Do you think that would work? If so, any tips on how to link queries/modules between databases. I'd say the module would be the main problem. The code I've got in it is:

Function CombineTables()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryMakeNthData"
    DoCmd.OpenQuery "qryAppendSthData"
    DoCmd.OpenQuery "qryCleanAssetTypeDesktop"
    DoCmd.OpenQuery "qryCleanAssetTypeLaptop"
    DoCmd.OpenQuery "qryCleanAssetTypeRouter"
    DoCmd.OpenQuery "qryCleanAssetTypeServer"
    DoCmd.OpenQuery "qryCleanAssetTypeSoftware"
    DoCmd.OpenQuery "qryCleanAssetTypeOther"
    DoCmd.OpenQuery "qryCleanAssetTypeMisc"
    DoCmd.SetWarnings True
    MsgBox "Your data is updated"
End Function

How would I run this code in a different database?

Thanks again for the help.

 

[tails]

Well unfortunantely that isn't my area of study so i cant really help you too much with access coding. I do a bit of programming but not enough in access to understand it fully, you'll need a bit more help than me for this task. The only other thing i can think of is make another administrator user and see if it works on that database... also what OS are you running?