creating table from recordset ?

  • Thread starter Thread starter Zlatko Matiæ
  • Start date Start date
Z

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 ?
 
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.
 
So, what to do ?

Douglas J. Steele said:
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.
Click to expand...
 
Check whether the (source) FieldType is Text on not and use CreateField
appropriately???

I thought that was clear from Douglas' reply.
 
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
 
Back
Top