use sql in vbscript to make new table?

[mattbold]

hello,

can anyone help me with this problem perchance? :

i need to create a new table when a button on a form is clicked on, using the entries in a list box as the column names in the new table, am i right in thinking i need to use the SQL "CREATE TABLE tablename (column1 TEXT, column2 TEXT)" ?? if so how do i integrate it into my code??

thanks,
matt

 

[ide]

yes
or if the rowsource of your list box is a query, with DAO:

Dim rst As DAO.Recordset
Dim tdfNew As TableDef
Dim i As Integer
'Create a new tabledef
Set tdfNew = CurrentDb.CreateTableDef("myTableName")
'create a recordset to get the names of fields, create field in the tabledef
Set rst = CurrentDb.OpenRecordset(Me.lstMyList.RowSource)
With tdfNew
    For i = 0 To rst.Fields.Count - 1
        .Fields.Append .CreateField(rst.Fields(i).Name, rst.Fields(i).Type)
    Next i
    CurrentDb.TableDefs.Append tdfNew
    CurrentDb.TableDefs.Refresh
End With
DoCmd.SetWarnings False
'insert values into the new table
DoCmd.RunSQL ("INSERT INTO myTableName " & Me.lstMyList.RowSource)
DoCmd.SetWarnings True

ide