creating a table

[Rick]

I need to create a table in code with certain fields that
I have stored in a recordset. Is there a way to do it
other than a MakeTable sql? How would I add the fields?

Thanks.

Rick

 

[Brendan Reynolds]

Public Sub TableFromRecordset()

Dim db As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim fldSource As DAO.Field
Dim fldTarget As DAO.Field
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set rstSource = db.OpenRecordset("SELECT * FROM tblTest")
Set tdf = db.CreateTableDef("MyNewTable")
For Each fldSource In rstSource.Fields
Set fldTarget = tdf.CreateField(fldSource.name, fldSource.Type,
fldSource.Size)
tdf.Fields.Append fldTarget
Next fldSource
db.TableDefs.Append tdf
Set rstTarget = db.OpenRecordset("SELECT * FROM MyNewTable")
Do Until rstSource.EOF
rstTarget.AddNew
For Each fldSource In rstSource.Fields
rstTarget.Fields(fldSource.name).Value = fldSource.Value
Next fldSource
rstTarget.Update
rstSource.MoveNext
Loop
rstSource.Close
rstTarget.Close

Application.RefreshDatabaseWindow

End Sub

 

[Rick]

Brendan,

Thanks, but my this looks like it is creating a table
using fields from an existing table. My fields for the new
table are stored as records in the source table. I can
loop through the recordset getting the field name but I'm
getting a data type conversion error. I think it may be in
trying to assign the Type and Size properties? I set up a
FieldType and a FieldSize field in the source table and
filled the records with Text and 150 but I still get the
error. What am I doing wrong?

Thanks again. Rick

 

[Brendan Reynolds]

Sorry, Rick, I'm afraid I don't understand your description of what you're
trying to do.