How do I determine the data type of a column?

[martindavey]

How would I determine the data type of a column on a table called myTable for example.

Is there a system table called syscolumns or something?

 

[JoeMiller]

Go into Design View of the table and look at the second column. That will tell you the data type.

Joe Miller
joe.miller@flotech.net

 

[skydive]

Hi Martin,

I think this will work!


Sub martin_test_01()
Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim i As Integer


Set db = CurrentDb
sql = "select Col1, Col2, Col3 from myTable;"

Set rs = db.OpenRecordset(sql)

If Not rs.EOF Then
For i = 0 To 2
MsgBox "Type = " & rs(i).Type
Next i
End If
End Sub

This will give you an integer constant return which is defined as shown below:


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

Hope this works and is helpful

Blue Skies!
Skydive

 

[LonnieJohnson]

The data type is determined at the time the table is implemented. If you wish to change this or view the type of an existing table. Go to the tables design view. Find the field/coulumn name in question and to the right will be the Data Type. Here you can change it. At the bottom in the "Field Properties" section you can format and set certain properties for this field.

Hope this is what you wanted...

B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[martindavey]

Thanks for the replies

I was looking for a programmatic solution (ASP to be precise).

The code provided by Skydive looks like the sort of thing I need, that's in VBA by the looks of it?

As long as the ADO recordset object supports a 'type' property I'll be okay.

Thanks.

 

[mymou]

Dont know about ASP but:

Public Sub fldType()

Dim oRS As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim oConn As New ADODB.Connection

sFileName = "TestDataBase.mdb"
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sFileName & ";Persist Security Info=False"

Set oRS = New ADODB.Recordset
oRS.Open "tblTableName", oConn, , , adCmdTable

Debug.Print "Fields in Employee Table:" & vbCr

For Each fldLoop In oRS.Fields
Debug.Print " Name: " & fldLoop.Name & vbCr & _
" Type: " & fldLoop.Type & vbCr
Next fldLoop

End Sub