Enter the names of the database, original table and field (Addresses, State), the lookup table and field (State, StateAbbrev), and the relationship that should link the tables (leave this blank if you don't want to create a relationship).

When you click the Go button, the program opens the database. It then deletes any relation, index, and lookup table that it created in a previous run.

Next the code creates the new lookup table and adds the lookup field to it. It uses the same data type and size as the field in the original table (Addresses.State).

The lookup table's field should contain unique values (it wouldn't make sense to have two records with the value "CO" in the States table) so the program adds a unique index to the lookup field (States.StateAbbrev).

Now the program uses an INSERT INTO statement to copy the distinct values from the original field (Addresses.State) to the lookup field (States.StateAbbrev). Notice all of the [brackets]. These are only necessary if the names you pick are reserved words in Access. For example, if you name the table Table. This is a bad practice and it is better to avoid those keywords. If you are willing to do that, then remove all the brackets and the program will crash if you accidentally use an invalid name.

If you entered a relation name, the program then creates a many-to-one relation linking the two fields. This relation allows one lookup field value to correspond to many records in the original table. That makes sense because many Addresses records might share the same State value.

If you do not create this relationship, your code can still use the lookup table to make a list of the allowed states but the database will not enforce the integrity constraint for you.

' Note that the [brackets] are only necessary if the table
' or field
' names are Access reserved words.
Private Sub cmdGo_Click()
Dim db As DAO.Database
Dim new_tabledef As DAO.TableDef
Dim new_field As DAO.Field
Dim old_field As DAO.Field
Dim new_index As DAO.Index
Dim new_relation As DAO.Relation
Dim relation_field As DAO.Field
Dim sql As String

    ' Open the database.
    Set db = _
        DBEngine.Workspaces(0).OpenDatabase(txtDatabase.Text, _
        False, False)

    ' Remove junk from previous runs if it exists.
    On Error Resume Next
    db.Relations.Delete txtRelation.Text
    db.TableDefs(txtNewTable.Text).Indexes.Delete "idx" & _
        txtNewField.Text
    db.TableDefs.Delete txtNewTable.Text
    On Error GoTo 0

    ' Make the new table.
    Set new_tabledef = db.CreateTableDef(txtNewTable.Text)
    Set old_field = _
        db.TableDefs(txtOldTable.Text).Fields(txtOldField.Text)
    Set new_field = _
        new_tabledef.CreateField(txtNewField.Text, _
        old_field.Type, old_field.Size)
    new_tabledef.Fields.Append new_field
    db.TableDefs.Append new_tabledef

    ' Make the new field unique.
    Set new_index = new_tabledef.CreateIndex("idx" & _
        txtNewField.Text)
    new_index.Fields.Append _
        new_index.CreateField(txtNewField.Text)
    new_index.Unique = True
    new_tabledef.Indexes.Append new_index

    ' Copy the values into the new table.
    sql = "INSERT INTO [" & _
        txtNewTable.Text & "] ([" & _
        txtNewField.Text & "]) " & _
        "SELECT DISTINCT [" & txtOldField.Text & _
        "] FROM [" & txtOldTable.Text & "]"
    'Debug.Print sql
    db.Execute sql

    ' If the relation name is non-blank, then
    ' make the relation between the tables.
    If Len(txtRelation.Text) > 0 Then
        Set new_relation = db.CreateRelation( _
            txtRelation.Text, _
            txtNewTable.Text, _
            txtOldTable.Text)
        Set relation_field = _
            new_relation.CreateField(txtNewField.Text)
        relation_field.ForeignName = txtOldField.Text
        new_relation.Fields.Append relation_field
        db.Relations.Append new_relation
    End If

    ' Close the database.
    db.Close

    MsgBox "Done"
End Sub

For information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.