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 Reynolds (MVP)


"Rick" <(E-Mail Removed)> wrote in message
news:006f01c50d4d$29291bd0$(E-Mail Removed)...
>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,

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
>-----Original Message-----
>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 Reynolds (MVP)
>
>
>"Rick" <(E-Mail Removed)> wrote in
message
>news:006f01c50d4d$29291bd0$(E-Mail Removed)...
>>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
>
>
>.
>

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

--
Brendan Reynolds (MVP)


"Rick" <(E-Mail Removed)> wrote in message
news:07ff01c50df2$77746290$(E-Mail Removed)...
> 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
>>-----Original Message-----
>>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 Reynolds (MVP)
>>
>>
>>"Rick" <(E-Mail Removed)> wrote in
> message
>>news:006f01c50d4d$29291bd0$(E-Mail Removed)...
>>>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
>>
>>
>>.
>>