Allowing user rights to create SQL Tables

[zenenigma]

I'm not sure if I should post this here or in the SQL area, because it deals with both SQL and VB.

Right now I have an application which allows users to browse through SQL tables and save portions of the tables to an access database on the network. Recently this access database has been going corrupt at least once a day (and repairing it doesn't always fix it). The database is not too big, we've recreated it many times, so it leads me to believe that the problem is too many users in that database at one time.

What are the problems with writing VB code to allow users to save the portions of the main SQL tables to a SQL table that they can create? I would need to allow them write(create), edit, read, and delete(for a few people).

Do SQL tables/databases corrupt as much as Access Tables/Databases? The users would definately have their own SQL database to store all of these tables.

Any advice/suggestions/links to other posts (though I couldn't find any posts related to this topic) would be greatly appreciated.

ZenEnigma

 

[Guest_imported]

You'll have to give your users Create Table permissions in the SQL server database. All of the other permissions mentioned apply to individual tables. These permissions can be set through SQL server or VB code. But since the users will have their own DB, you might as well just make them all database owners (DBO). That should solve all of your permission issues.

SQL Server tables are much more reliable than MS Access tables, and you will be able to backup all of the data in the table when you run the backup for that server.

 

[CajunCenturion]

You should be able to use the ADOX objects to provide the ability for user's to create and maintain tables. Include a reference to the ADO Extensions for DDL and Security to gain access to these objects. You might want to, from within your VB front end, provide some sort of checks to insure that only certain tables may be altered.

SQL Server is much more stable than Access. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein