create table VBA or SQL

[michael61182]

Hello,

I'm trying to create a form that will create a new table with a specified table name.

I stole this code from somewhere which when I click the button it creates a table named "Table1New1".

Private Sub Command4_Click()
Dim db As Database
    
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "CREATE TABLE [Table1New1] (ID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Role TEXT);"
    db.Execute strSQL
    Set db = Nothing
End Sub

However I would like the user to either be prompted for the table name or for the value of a new record to be stored as a string to be used as the table name.

I've also been able to create a query using the following code in Data Definition to create a table, but I'm still having the same issue.

[
CREATE TABLE  NewTable
(ID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY);
 

/CODE]

The idea is to have a table that is a list of 'Roles'
For each 'Role' I would like a separate table with the name of the 'Role', because each 'Role' will have 'Sub Roles'

So on my form that lists all the 'Roles', I'd like to either enter a new record which would in turn create a table with that record value as the new table name or to just add a button which will prompt for the new 'Role'

Thanks in advance.

 

[Joe4]

For each 'Role' I would like a separate table with the name of the 'Role', because each 'Role' will have 'Sub Roles'

That really does not sound like good database design. In general, for any table, each new piece of data should not necessitate a new table being created.

Unless the fields you need for each Sub Role is vastly different (so the tables cannot be structured similarly), you should only really have one "Sub Roles" table, that has fields Role and Sub Role, along with whatever other fields you need. Otherwise, in order to connect your Role and Sub Roles tables, you would need to do it manually for each and every one (as opposed to simply joining the two tables on the Role field that exists in each one).