field.Type

field.Type
十进制
dbBigInt= 16
dbBinary= 9
dbBoolean= 1
dbByte= 2
dbChar= 18
dbCurrency= 5
dbDate= 8
dbDecimal= 20
dbDouble= 7
dbFloat= 21
dbGUID= 15
dbInteger= 3
dbLong= 4
dbLongBinary= 11
dbMemo= 12
dbNumeric= 19
dbSingle= 6
dbText= 10
dbTime= 22
dbTimeStamp= 23
dbVarBinary= 17
============================================================================

e1: access 97
Set db = OpenDatabase("d:\sample\test.mdb")
Set tdf = db.TableDefs("t1")
For i = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Fields(i).Name & "--" & tdf.Fields(i).Type
Next
//t1_col1--4 dbLong= 4 自动编号 长整型
//t1_col2--2 dbByte= 2 数字 字节
//t1_col3--3 dbInteger= 3 数字 整型
//t1_col4--4 dbLong= 4 数字 长整型
//t1_col5--6 dbSingle= 6 数字 单精度型
//t1_col6--7 dbDouble= 7 数字 双精度型
//t1_col7--15 dbGUID= 15 数字 同步复制 ID
//t1_col8--10 dbText= 10 文本
//t1_col9--12 dbMemo= 12 备注
//t1_col10--8 dbDate= 8 日期/时间
//t1_col11--5 dbCurrency= 5 货币
//t1_col12--15 dbGUID= 15 自动编号 同步复制 ID
//t1_col13--1 dbBoolean= 1 是/否
//t1_col14--11 dbLongBinary= 11 OLE对象
//t1_col15--12 dbMemo= 12 超级链接
//t1_col16--10 dbText= 10 文本

============================================================================

sql server 2000 dao
3 t1_col1 int 4 0---t1_col1--4 dbLong= 4
0 t1_col2 int 4 1 t1_col2--4 dbLong= 4
0 t1_col3 image 16 1 t1_col3--11 dbLongBinary= 11
0 t1_col4 bigint 8 1 t1_col4--16 dbBigInt= 16
0 t1_col5 binary 50 1 t1_col5--9 dbBinary= 9
0 t1_col6 bit 1 1 t1_col6--1 dbBoolean= 1
0 t1_col7 char 10 1 t1_col7--18 dbChar= 18
0 t1_col8 datetime 8 1 t1_col8--23 dbTimeStamp= 23
0 t1_col9 decimal 9 1 t1_col9--20 dbDecimal= 20
0 t1_col10 float 8 1 t1_col10--21 dbFloat= 21
0 t1_col11 image 16 1 t1_col11--11 dbLongBinary= 11
0 t1_col12 int 4 1 t1_col12--4 dbLong= 4
0 t1_col13 money 8 1 t1_col13--20 dbDecimal= 20
0 t1_col14 nchar 10 1 t1_col14--0
0 t1_col15 ntext 16 1 t1_col15--0
0 t1_col16 numeric 9 1 t1_col16--19 dbNumeric= 19
0 t1_col17 nvarchar 50 1 t1_col17--0
0 t1_col18 real 4 1 t1_col18--6 dbSingle= 6
0 t1_col19 smalldatetime 4 1 t1_col19--23 dbTimeStamp= 23
0 t1_col20 smallint 2 1 t1_col20--3 dbInteger= 3
0 t1_col21 smallmoney 4 1 t1_col21--20 dbDecimal= 20
0 t1_col22 sql_variant 1 t1_col22--9 dbBinary= 9
0 t1_col23 text 16 1 t1_col23--12 dbMemo= 12
0 t1_col24 timestamp 8 1 t1_col24--9 dbBinary= 9
0 t1_col25 tinyint 1 1 t1_col25--2 dbByte= 2
0 t1_col26 uniqueidentifier16 1 t1_col26--1055
0 t1_col27 varbinary 50 1 t1_col27--17 dbVarBinary= 17
0 t1_col28 varchar 50 1 t1_col28--10 dbText= 10
0 t1_col29 empid (char) 9 1 t1_col29--18 dbChar= 18
0 t1_col30 id (varchar) 11 1 t1_col30--10 dbText= 10
0 t1_col31 tid (varchar) 6 1 t1_col31--10 dbText= 10
====================================================================================================

e3: 1/5 tabledef.field.type
1.Set db = OpenDatabase("d:\sample\test.mdb")
Set tdf = db.TableDefs("t1")
For i = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Fields(i).Name & "--" & tdf.Fields(i).Type
Next
//t1_col1--4 (dbLong=4)
//t1_col2--2
Debug.Print "-------------------------"
Set fld = tdf.CreateField("new", dbInteger)
fld.Type = dbLong
Debug.Print fld.Type //4
tdf.Fields.Append fld
Debug.Print "-------------------------------------"
For i = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Fields(i).Name & "--" & tdf.Fields(i).Type
Next
//t1_col1--4 (dbLong=4)
//t1_col2--2
//new--4
2.Set db = OpenDatabase("d:\sample\test.mdb")
Set tdf = db.TableDefs("t1")
For i = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Fields(i).Name & "--" & tdf.Fields(i).Type
Next
//t1_col1--4
//t1_col2--2
//new--4
3.Set db = OpenDatabase("d:\sample\test.mdb")
Set tdf = db.TableDefs("t1")
For i = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Fields(i).Name & "--" & tdf.Fields(i).Type
Next
//t1_col1--4
//t1_col2--3
//new--4
Debug.Print dbInteger //3

tdf.Fields("t1_col2").Type = dbInteger //error
=====================================================================================
e4: 2/5 index.field.type
Set db = OpenDatabase("d:\sample\test.mdb")
Set tdf = db.TableDefs("t1")
Set idx = tdf.Indexes("ind1")
For i = 0 To idx.Fields.Count - 1
Debug.Print idx.Fields(i).Name
Next
//t1_col1
//t1_col2
Debug.Print "------------------"
Debug.Print idx.Fields("t1_col2").Name //t1_col2
Debug.Print idx.Fields("t1_col2").Type //error
or idx.Fields("t1_col2").Type = dbInteger //error
=====================================================================================
e5: 3/5 querydef.field.type
Set db = OpenDatabase("d:\sample\test.mdb")
Set qdf = db.CreateQueryDef("", "select * from t1")
Set rst = qdf.OpenRecordset()
Debug.Print rst(0).Name & "--" & rst(1).Name
Do Until rst.EOF
Debug.Print rst(0) & "--" & rst(1)
rst.MoveNext
Loop
//t1_col1--t1_col2
//29--1
//30--2
//31--3
//32--4
//33--5
Debug.Print "--------------------------------------"
For i = 0 To qdf.Fields.Count - 1
Debug.Print qdf.Fields(i).Name & "--" & qdf.Fields(i).Type
Next

//t1_col1--4
//t1_col2--3
//new--4
Debug.Print "--------------------------------"
Debug.Print qdf.Fields("t1_col2").Type //3
qdf.Fields("t1_col2").Type = dbInteger //error
==============================================================================
e6: 4/5 recordset.field.type
Set db = OpenDatabase("d:\sample\test.mdb")
Set qdf = db.CreateQueryDef("", "select * from t1")
Set rst = qdf.OpenRecordset()
Debug.Print rst(0).Name & "--" & rst(1).Name
Do Until rst.EOF
Debug.Print rst(0) & "--" & rst(1)
rst.MoveNext
Loop
//t1_col1--t1_col2
//29--1
//30--2
//31--3
//32--4
//33--5
Debug.Print "--------------------------------------"
For i = 0 To rst.Fields.Count - 1
Debug.Print rst.Fields(i).Name & "--" & rst.Fields(i).Type
Next
//t1_col1--4
//t1_col2--3
//new--4
Debug.Print "--------------------------------------"
Debug.Print rst.Fields("t1_col2").Type //3
rst.Fields("t1_col2").Type = dbInteger //error

===============================================================================
e7: 5/5 relation.field.type
1.Set db = OpenDatabase("d:\sample\test.mdb")
Set rel = db.CreateRelation()
rel.Name = "aa"
rel.Table = "t1"
rel.ForeignTable = "t2"
rel.Attributes = dbRelationUpdateCascade
rel.Fields.Append rel.CreateField("t1_col1")
rel.Fields.Append rel.CreateField("t1_col2")
rel.Fields("t1_col1").ForeignName = "t2_col2"
rel.Fields("t1_col2").ForeignName = "t2_col3"
db.Relations.Append rel
2.Set db = OpenDatabase("d:\sample\test.mdb")
Set rel = db.Relations("aa")
For i = 0 To rel.Fields.Count - 1
Debug.Print rel.Fields(i).Name
Next
//t1_col1
//t1_col2
Debug.Print "----------------------------------"
Debug.Print rel.Fields("t1_col2").Name //t1_col2
Debug.Print rel.Fields("t1_col2").Type //error
or rel.Fields("t1_col2").Type = dbInteger //error
============================================================================