creating table from recordset ?

[Zlatko Matiæ]

Hello.
How to programaticcaly create a table from a recordset ?
I tried something like this:
qryrs is recordset created by a querydef (pass-through query)...


Set tbl = db.CreateTableDef("TableNAme")

For I = 0 To qryrs.Fields.Count - 1

FieldName = qryrs.Fields(I).Name
FieldType = qryrs.Fields(I).Type
FieldSize = qryrs.Fields(I).Size

Set fld = tbl.CreateField(FieldName, FieldType, FieldSize)

' Append field to Fields collection
tbl.Fields.Append fld

Next I

' Append table to TableDef collection
db.TableDefs.Append tbl


But I have an error concerning data type of FieldType....
What is wrong ?

 

[Douglas J. Steele]

You sure the problem is the type, or might it be the size? Size doesn't
exist for most of the field types: Text is the only type where it does.

 

[Zlatko Matiæ]

So, what to do ?

You sure the problem is the type, or might it be the size? Size doesn't
exist for most of the field types: Text is the only type where it does.

 

[Van T. Dinh]

Check whether the (source) FieldType is Text on not and use CreateField
appropriately???

I thought that was clear from Douglas' reply.

 

[Douglas J. Steele]

Well, to make it more explicit...

Set tbl = db.CreateTableDef("TableNAme")

For I = 0 To qryrs.Fields.Count - 1
FieldName = qryrs.Fields(I).Name
FieldType = qryrs.Fields(I).Type

If FieldType <> dbText Then
Set fld = tbl.CreateField(FieldName, FieldType)
Else
FieldSize = qryrs.Fields(I).Size
Set fld = tbl.CreateField(FieldName, FieldType, FieldSize)
End If

' Append field to Fields collection
tbl.Fields.Append fld

Next I