creating tables

[elidag]

I want to create a new table with colume headings generated from data in another table. As a fairly new sql user please keep any explanations simple if possible

 

[oharab]

I must admit, I tend not to use createtable queries, prefering to use DAO, mainly because I forgot Create Table existed and just got used to using code. Looking at the help file, I can't see how you can do this, tho I am not the worlds greatest SQL writer.

To do what you want in code:

Sub MakeTableFromData()
Dim FieldTable As String, NewTable As String
Dim dB As DAO.Database, rst As DAO.Recordset
Dim td As DAO.TableDef, fld As DAO.Field

FieldTable = "tblData"
NewTable = "tblNew"

Set dB = CurrentDb
Set rst = dB.OpenRecordset(FieldTable)
Set td = dB.CreateTableDef(NewTable)
Do Until rst.EOF
Set fld = td.CreateField(rst!Data, dbText, 255)
td.Fields.Append fld
rst.MoveNext
Loop
dB.TableDefs.Append td
rst.Close
Set rst = Nothing
Set fld = Nothing
Set td = Nothing
Set dB = Nothing
End Sub

Basically the code loops through each record in the FieldTable and adds a new field of type text and size 255 characters with the name of the data in the FieldTable.
I have created an example file at:

http://oharab.hypermart.net/files/MakeTable.ZIP

There's no comments, but it should get you started.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------