Modifying permissions via VBA

[PaulA]

I have a report that is based on table that is created when the report is generated. Prior to a report being generated, the previous table is deleted with a new table with new data created.

The users who will generate this report do not have administrative rights. They do not have permission to delete or modify table structures, only data.

How can I get this report to run for them? Can I give temporary permission to their user group for administrative rights and remove them at the end of the code?

How do I do this?

Thanks.

Paul

 

[boblarson]

Why not just change the make table query to a regular query to display the data you need and base the report off of the query instead of the table? Then your permissions problem wouldn't be a problem.

 

[PaulA]

This table must be dynamic in that the field names can change each time the report is generated, based on the data.

I am not using SQL to create a table. I am creating a tabledef in VBA with fields based on subject names which may be modified along the way. The tabledef is deleted and then recreated each time the report is generated. Records are then added to a recordset from the table using data from multiple queries for the different data elements.

Thanks.

 

[RichMorrison]

Paul,

Maybe I am missing something here.

If you create and delete tabledefs with VBA code, then the users don't need "modify" rights to table design. The users just need basic read, write, delete, etc for the tables.

RichM

 

[PaulA]

The tabledef (and the appended table to the collection) are actually deleted and recreated because the table may have modified field names each time it is created.

The user does not have this level of permission. We're talking about deleting the table, not records in the table.

Thanks.

 

[RichMorrison]

Paul,

You wrote
<<
The user does not have this level of permission. We're talking about deleting the table, not records in the table.
>>

Yes that's what you wrote and what I wrote the first time around.

You said you **CREATE** and **DELETE** tabledefs with "code". I take that to mean a module. That means a specific user just needs permission to execute the module. The module itself does NOT need permission to create or delete a tabledef.

N'est pas ?

RichM

 

[PaulA]

It's a class module behind the report, not a stand-alone module.
The user is getting a permission message stating he doesn't have permission to use the table. This message went away when I added "modify design" permissions to his group. I'm not thrilled with that solution, though--(however, they don't have access to the tables so it probably doesn't really matter).

Thanks.