Type Property

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Field object, Forward-Only-Type Recordset object, Parameter object, Property object, QueryDef object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object, Workspace object.

Description

Sets or returns a value that indicates the operational type or data type of an object.

Settings and Return Values

The setting or return value is a constant that indicates an operational or data type. For a Field or Property object, this property is read/write until the object is appended to a collection or to another object, after which it's read-only. For a QueryDef, Recordset, or Workspace object, the property setting is read-only. For a Parameter object in a Microsoft Jet workspace the property is read-only, while in an ODBCDirect workspace the property is always read/write.

For a Field, Parameter, or Property object, the possible settings and return values are described in the following table.

Constant	Description

dbBigInt	Big Integer
dbBinary	Binary
dbBoolean	Boolean
dbByte	Byte
dbChar	Char
dbCurrency	Currency
dbDate	Date/Time
dbDecimal	Decimal
dbDouble	Double
dbFloat	Float
dbGUID	GUID
dbInteger	Integer
dbLong	Long
dbLongBinary	Long Binary (OLE Object)
dbMemo	Memo
dbNumeric	Numeric
dbSingle	Single
dbText	Text
dbTime	Time
dbTimeStamp	Time Stamp
dbVarBinary	VarBinary

For a QueryDef object, the possible settings and return values are shown in the following table.

Constant	Query type

dbQAction	Action
dbQAppend	Append
dbQCompound	Compound
dbQCrosstab	Crosstab
dbQDDL	Data-definition
dbQDelete	Delete
dbQMakeTable	Make-table
dbQProcedure	Procedure (ODBCDirect workspaces only)
dbQSelect	Select
dbQSetOperation	Union
dbQSPTBulk	Used with dbQSQLPassThrough to specify a query that doesn't return records (Microsoft Jet workspaces only)
dbQSQLPassThrough	Pass-through (Microsoft Jet workspaces only)
dbQUpdate	Update

Note To create an SQL pass-through query in a Microsoft Jet workspace, you don't need to explicitly set the Type property to dbQSQLPassThrough. The Microsoft Jet database engine automatically sets this when you create a QueryDef object and set the Connect property.

For a Recordset object, the possible settings and return values are as follows.

Constant	Recordset type

dbOpenTable	Table (Microsoft Jet workspaces only)
dbOpenDynamic	Dynamic (ODBCDirect workspaces only)
dbOpenDynaset	Dynaset
dbOpenSnapshot	Snapshot
dbOpenForwardOnly	Forward-only

For a Workspace object, the possible settings and return values are as follows.

Constant	Workspace type

dbUseJet	The Workspace is connected to the Microsoft Jet database engine.
dbUseODBC	The Workspace is connected to an ODBC data source.

Remarks

When you append a new Field, Parameter, or Property object to the collection of an Index, QueryDef, Recordset, or TableDef object, an error occurs if the underlying database doesn't support the data type specified for the new object.

Specifics (Microsoft Access)

In Microsoft Access, you can set the data type for a field in table Design view, and you can set the data type for a parameter in the Query Parameters dialog box. These actions are equivalent to setting the DAO Type property for a Field object or a Parameter object by using Visual Basic.

The following table lists the DAO Type property constants and the corresponding Microsoft Access settings for field and parameter data types if you are setting them from table Design view or the Query Parameters dialog box.

When creating a table field with data type Number, set the FieldSize property to specify which of the six numeric data types the field will be. By default this property is set to Long Integer. The other settings are Byte, Integer, Single, Double, and ReplicationID.

Constant	Table field setting	Query parameter setting

dbBoolean	Yes/No	Yes/No
dbByte	Number (FieldSize = Byte)	Byte
dbCurrency	Currency	Currency
dbDate	Date/Time	Date/Time
dbDouble	Number (FieldSize = Double)	Double

(continued)

Constant	Table field setting	Query parameter setting

dbGUID	Number or AutoNumber (FieldSize = ReplicationID)	Not supported
dbInteger	Number (FieldSize = Integer)	Integer
dbLong	Number (FieldSize = Long Integer)	Long Integer
	AutoNumber (FieldSize = Long Integer)	Not supported
dbLongBinary	OLE Object	OLE Object
	Not supported	Binary
dbMemo	Memo	Memo
dbSingle	Number (FieldSize = Single)	Single
dbText	Text	Text
Not supported	Not supported	Value

Note The parameter data type Value doesn't correspond to a data type defined by the Microsoft Jet database engine. It corresponds to the SQL reserved word VALUE, which can be used to create a parameter query. In Microsoft Access or SQL queries, VALUE can be considered a valid synonym for the Visual Basic Variant data type.

Example

This example demonstrates the Type property by returning the name of the constant corresponding to the value of the Type property of four different Recordsets. The RecordsetType function is required for this procedure to run.

Sub TypeX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Default is dbOpenTable.
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")
    Debug.Print _
        "Table-type recordset (Employees table): " & _
        RecordsetType(rstEmployees.Type)
    rstEmployees.Close

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenDynaset)
    Debug.Print _
        "Dynaset-type recordset (Employees table): " & _
        RecordsetType(rstEmployees.Type)
    rstEmployees.Close

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenSnapshot)
    Debug.Print _
        "Snapshot-type recordset (Employees table): " & _
        RecordsetType(rstEmployees.Type)
    rstEmployees.Close

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenForwardOnly)
    Debug.Print _
        "Forward-only-type recordset (Employees table): " & _
        RecordsetType(rstEmployees.Type)
    rstEmployees.Close

    dbsNorthwind.Close

End Sub

Function RecordsetType(intType As Integer) As String

    Select Case intType
        Case dbOpenTable
            RecordsetType = "dbOpenTable"
        Case dbOpenDynaset
            RecordsetType = "dbOpenDynaset"
        Case dbOpenSnapshot
            RecordsetType = "dbOpenSnapshot"
        Case dbOpenForwardOnly
            RecordsetType = "dbOpenForwardOnly"
    End Select

End Function

This example demonstrates the Type property by returning the name of the constant corresponding to the value of the Type property of all the Field objects in the Employees table. The FieldType function is required for this procedure to run.

Sub TypeX2()

    Dim dbsNorthwind As Database
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    Debug.Print "Fields in Employees TableDef:"
    Debug.Print "    Type - Name"

    ' Enumerate Fields collection of Employees table.
    For Each fldLoop In _
        dbsNorthwind.TableDefs!Employees.Fields
        Debug.Print "        " & FieldType(fldLoop.Type) & _
            " - " & fldLoop.Name
    Next fldLoop

    dbsNorthwind.Close

End Sub

Function FieldType(intType As Integer) As String

    Select Case intType
        Case dbBoolean
            FieldType = "dbBoolean"
        Case dbByte
            FieldType = "dbByte"
        Case dbInteger
            FieldType = "dbInteger"
        Case dbLong
            FieldType = "dbLong"
        Case dbCurrency
            FieldType = "dbCurrency"
        Case dbSingle
            FieldType = "dbSingle"
        Case dbDouble
            FieldType = "dbDouble"
        Case dbDate
            FieldType = "dbDate"
        Case dbText
            FieldType = "dbText"
        Case dbLongBinary
            FieldType = "dbLongBinary"
        Case dbMemo
            FieldType = "dbMemo"
        Case dbGUID
            FieldType = "dbGUID"
    End Select

End Function

This example demonstrates the Type property by returning the name of the constant corresponding to the value of the Type property of all the QueryDef objects in Northwind. The QueryDefType function is required for this procedure to run.

Sub TypeX3()

    Dim dbsNorthwind As Database
    Dim qdfLoop As QueryDef

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    Debug.Print "QueryDefs in Northwind Database:"
    Debug.Print "    Type - Name"

    ' Enumerate QueryDefs collection of Northwind database.
    For Each qdfLoop In dbsNorthwind.QueryDefs
        Debug.Print "        " & _
            QueryDefType(qdfLoop.Type) & " - " & qdfLoop.Name
    Next qdfLoop

    dbsNorthwind.Close

End Sub

Function QueryDefType(intType As Integer) As String

    Select Case intType
        Case dbQSelect
            QueryDefType = "dbQSelect"
        Case dbQAction
            QueryDefType = "dbQAction"
        Case dbQCrosstab
            QueryDefType = "dbQCrosstab"
        Case dbQDelete
            QueryDefType = "dbQDelete"
        Case dbQUpdate
            QueryDefType = "dbQUpdate"
        Case dbQAppend
            QueryDefType = "dbQAppend"
        Case dbQMakeTable
            QueryDefType = "dbQMakeTable"
        Case dbQDDL
            QueryDefType = "dbQDDL"
        Case dbQSQLPassThrough
            QueryDefType = "dbQSQLPassThrough"
        Case dbQSetOperation
            QueryDefType = "dbQSetOperation"
        Case dbQSPTBulk
            QueryDefType = "dbQSPTBulk"
    End Select

End Function

Example (Microsoft Access)

See the Size property example (Microsoft Access).

Example (Microsoft Excel)

See the Size property example (Microsoft Excel).