Create tables automatically from user input

[Umbane]

I would like to make tables for user input as to what tables are required and what fields need to be in the table.

For example:
Have a table that has the following fields

TableNameID
FieldName
DataType
Description

Then have a form that the user types this information in, and a command button to create the table based on the user input.

Is this possible to do or is there a better way to do it?

 

[Craig0201]

Isn't that basically Tables Design View?

What are you trying to achieve?

 

[easyit]

Hi,

You could use:

    If Not ObjectExists("table", "tablename") Then
        strSQL = "CREATE TABLE t_log ( [key] COUNTER CONSTRAINT ndxkey PRIMARY KEY, [nt_account] TEXT(25), [currentuser] TEXT(30), [datum] DATETIME, [tijd] TIME, [error_msg] MEMO, [AtLocation] TEXT(25))"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If


Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
    Dim DB As DAO.Database
    Dim tbl As TableDef
    Dim qry As QueryDef
    Dim i As Integer

    Set DB = CurrentDb()
    ObjectExists = False

    If strObjectType = "Table" Then
        For Each tbl In DB.TableDefs
            If tbl.name = strObjectName Then
                ObjectExists = True
                GoTo Exit_Handler
            End If
        Next tbl
    ElseIf strObjectType = "Query" Then
        For Each qry In DB.QueryDefs
            If qry.name = strObjectName Then
                ObjectExists = True
                GoTo Exit_Handler
            End If
        Next qry
    ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
        For i = 0 To DB.Containers(strObjectType & "s").Documents.Count - 1
            If DB.Containers(strObjectType & "s").Documents(i).name = strObjectName Then
                ObjectExists = True
                GoTo Exit_Handler
            End If
        Next i
    ElseIf strObjectType = "Macro" Then
        For i = 0 To DB.Containers("Scripts").Documents.Count - 1
            If DB.Containers("Scripts").Documents(i).name = strObjectName Then
                ObjectExists = True
                GoTo Exit_Handler
            End If
        Next i
    Else
        MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
    End If

Exit_Handler:
    Exit Function
End Function

EasyIT

 

[JoeAtWork]

As Craig0201 pointed out, this is what the Table Designer is for. And if the users don't understand how to use this rather simple interface, that's a very good sign that they don't have an understanding of how to create a well designed, normalized database.

Letting untrained users create tables willy-nilly has "disaster" written all over it. You might as well just store all your data in a bunch of spreadsheets.

A better solution (IMHO): create a "Suggestions" table and a nice looking form that attaches to it. Have the users enter their suggestions for improvements for the database. Have somebody who knows how to design databases (and hopefully can program as well) review the suggestions and implement the good ones.