Access VBA Create Table (Simplified)

This post is an answer to someone else who wanted to create a table and field names based on form inputs.

So I simplified a past post:

http://vbahowto.com/how-to-create-a-table-in-access-using-sql-and-vba/

Here are the form inputs:

…and when you click the button, the following code runs:

Private Sub btnCreateTable_Click()
    CreateNewTable
    
    MsgBox "Table Created. (Press F5 on your keyboard to view it in the DB window)"
    
End Sub

Public Function CreateNewTable() As Boolean
  
    'create the new access table based on the form parameters:
    
    'use the current database and execute the Create Table SQL command
    DBEngine(0)(0).Execute "CREATE TABLE " & Nz(Me.txtTableName, "NoName") & " (ID COUNTER CONSTRAINT ID PRIMARY KEY, " & _
        Nz(Me.txtFieldName1, "NA1") & " CHAR(255) NOT NULL, " & _
        Nz(Me.txtFieldName2, "NA2") & " CHAR(255) NOT NULL, " & _
        Nz(Me.txtFieldName3, "NA3") & " CHAR(255) NOT NULL, " & _
        Nz(Me.txtFieldName4, "NA4") & " CHAR(255) NOT NULL, " & _
        Nz(Me.txtFieldName5, "NA5") & " CHAR(255) NOT NULL, " & _
        Nz(Me.txtFieldName6, "NA6") & " CHAR(255) NOT NULL)"

    'refresh the current db tables
    DBEngine(0)(0).TableDefs.Refresh

    CreateNewTable = True

End Function

…and a table is produced…

Let me know if you have any questions.

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

by