Security permissions are tricky business with make-table queries

[BGuidry]

I had the biggest problem trying to set a "Read-Only" group access to my database due to a make-table query that is fired (in a vba script) by the "Autoexec" macro (anything in a macro with this name fires when the database is opened, automatically. The reason I needed this to fire each time the database is opened is to keep the data in my main lookup-table fresh, as this is data used in many queries/forms in the database, and the data changes often. At first I set the Read-Only permission to this table at least to "Modify" the table. But, if I were to login with my Administrator/Owner login, and then again with a Read-Only login, it would drop the "Modify" permission to the table.

What I ended up doing, the only method I found that worked, was to give "Administer" access to the table being created each time by the make-table query, rather than just "Modify" permission. I also set the permission on the query to at least "Update Data" for the Read-Only group.

 

[lameid]

So you are saying you set the default permission for table objects to Administer for all groups? That would deal with the make table problem.

However, most likely creating a table on startup is not the way to go. Why are you creating a table?

 

[BGuidry]

I am creating a lookup table, for several other queries to utilize, as this data is updated often (in an Excel spreadsheet). I can't remember why I didn't just use this field from the linked Excel file, but something didn't work with that method. I think it had something to do with matching id's from a subform to the lookup field on a main form.