Use record as field names on new table

[NilsC]

I have a table that i would like to take the data from one record and use that data as Field Names in a new table.

The original table contains Field names from a Database on a UNIX server. So the first record is field names for table1 second record for table2 etc.

The tables vary in size so I need to handle empty fields(empty field is end of table) and the first field in the record is the name of the table and should be the table name. The original table have 40 fields and some of the tables uses only 5 to 10 fields.

Any help would be apreciated

 

[cmmrfrds]

What version of Access are you using?

 

[NilsC]

I apologize for the delay in getting back to you, I took a long weekend.
I’m on Access 97 with sp1

Thank you
Nils

 

[cmmrfrds]

Since you are using Access 97, then DAO is the best way to create the tables. You will need to read the Access table that contains the table and field names record by record and then use DAO to create the tables - one for each record. Here is an example of a DAO routine to create a table. Hopefully, that will get you started.

Sub DAOCreateTable()

Dim db As DAO.Database
Dim tbl As DAO.TableDef

' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb&quot

' Create a new TableDef object.
Set tbl = db.CreateTableDef("Contacts&quot

With tbl
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
.Fields.Append .CreateField("ContactName", dbText)
.Fields.Append .CreateField("ContactTitle", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)
.Fields("Notes&quot.Required = False
End With

' Add the new table to the database.
db.TableDefs.Append tbl

db.Close

End Sub