Create a TableDef with a type

[lamago]

Hi, Im trying to make a table using fields names, type, and size listed in another table named FileLayout. I get Type Conversion Error on the line where I create the fieldtype. Should I use something instead of String??

Function CreateTable()
Dim db As Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim dsp As String, fldname As String, fldtype, fldsize As Integer
Dim prp As DAO.Property
Dim sqlstr As String
Dim newtable As String

newtable = InputBox("Enter New table Name")

'Open Database
sqlstr = "select * from Filelayout;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
Set tdf = db.CreateTableDef(newtable)

With tdf
rs.MoveFirst
While Not rs.EOF
fldname = rs.Fields("FieldName")
fldtype = rs.Fields("FieldType")
fldsize = rs.Fields("FieldSize")
.Fields.Append .CreateField(fldname, fldtype, fldsize)
rs.MoveNext
Wend
End With
'Close Database
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

 

[Remou]

Hi
I am not quite sure what you mean, but this may help:

Dim tdfNew As TableDef
Dim rs As Recordset
Dim fldtype As Integer

Set rs = CurrentDb.OpenRecordset("tblTableDefinition")
' Create a new TableDef object.
Set tdfNew = CurrentDb.CreateTableDef(strTableName)

With tdfNew
' Create fields and append them to the new TableDef object.
    Do While Not rs.EOF
        fldtype = DLookup("TypeNumber", "tblDAOFieldTypes", "TypeText = '" & rs!FieldType & "'")
        If rs!FieldLength <> 0 Then
            .Fields.Append .CreateField(rs!FieldName, fldtype, rs!FieldLength)
        Else
            .Fields.Append .CreateField(rs!FieldName, fldtype)
        End If
        rs.MoveNext
    Loop
End With

CurrentDb.TableDefs.Append tdfNew
Set tdfNew = Nothing
rs.Close
Set rs = Nothing

tblDAOFieldTypes
[tt]Description TypeText TypeNumber
Boolean dbBoolean 1
Byte dbByte 2
Integer dbInteger 3
Long dbLong 4
Currency dbCurrency 5
Single dbSingle 6
Double dbDouble 7
Date/Time dbDate 8
Binary dbBinary 9
Text dbText 10
Long Binary (OLE Object) dbLongBinary 11
Memo dbMemo 12
GUID dbGUID 15
Big Integer dbBigInt 16
VarBinary dbVarBinary 17
Char dbChar 18
Numeric dbNumeric 19
Decimal dbDecimal 20
Float dbFloat 21
Time dbTime 22
TimeStamp dbTimeStamp 23[/tt]

 

[AlanJordan]

The answer is "Yes."

Dim newtable As String

A table is a structure that is much more complicated than a string. A string is simply a series of characters.

Dimension your table as a tabledef.

See the code posted by another member.

I hope this helps.

Alan

 

[lamago]

Thanks! This is very helpful. Am I correct that you cant change the type of table using VB after the tabledef is created? My table already has the data in it, so all i really need to do is change the type.

 

[Remou]

What do you mean by type of table? Did you mean type of field?

 

[lamago]

: ) Sorry, yes that is what I meant. The Type of a field

 

[Remou]

Hi
I do not think you can, with TableDefs. If certain conditions are met, you can use Alter Table:
[tt]SQL = "ALTER TABLE table1 ALTER COLUMN Field1 INT"
DoCmd.RunSQL SQL[/tt]
There is a chunk of ifs and perhapses with Alter Table, so please check out the help.