Create Table problem.

[Baesucks]

I have an MS-Access project using a SQL Server database. I have code running that needs to create a temporary table in the database. When I execute the code it works fine. When another user executes the code it gives them Error 262 - Create Table Permission denied in database...

The SQL Server uses Windows authentication. Other users have permission to read and write data (db_datareader, db_datawriter). I am the database owner which is why it must work for me.

The code being executed is simply...

    strSQL = "CREATE TABLE " & strTempTableName & " (Detail varchar (1000), RecordNumber integer)"
    DoCmd.RunSQL strSQL

Do I need to add a permission in SQL server to other users so they can create and drop a table?

 

[ajrimmer]

Personally, I would always create/drop tables from a stored procedure which will have appropriate permissions set against it to restrict who could execute them.

 

[danvlas]

Since it has the same structure all the time, you might find it useful to have a permanent table and just add/update records.
Just add some new columns : AddedBy, AddedDate, DeletedBy, DeletedDate and you will have an audit table, showing what happened and when.

Where Deleted Is Null would show you 'active' records



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html