creating a table

  • Thread starter Thread starter Rick
  • Start date Start date
R

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
 
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
 
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
 
Sorry, Rick, I'm afraid I don't understand your description of what you're
trying to do.
 
Back
Top