Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get the name of an ADO data type from the recordset.field.type property?

Tags:

vba

ms-access

I need to produce a list of the fields (name, type and size) from an Access table. This simple VB code gives me almost what I need:

Set rs = CurrentDb.OpenRecordset("myTable")
For x = 0 To rs.Fields.Count - 1
    Print #1, rs.Fields(x).Name & vbTab & rs.Fields(x).Type & vbTab & rs.Fields(x).Size
Next 

However the "Type" of course is a numeric constant like "10" instead of something like "Varchar".

I was going to do this:

Select Case rs.Fields(x).Type
  Case adChar
    fieldType = "adChar"
  Case adInteger
    fieldType = "adInteger"
  Case adDouble
ETCETERA....

But I wonder if there's a better way, something like DataTypeEnum.FindName(Type) or something?

Apologies in advance if this is a "stupid question", but I don't work in VB every day and Googling has produced no clear answer on this.

like image 993
user1071914 Avatar asked Nov 01 '13 21:11

user1071914


1 Answers

The answers in the comments under the question (above) are excellent, unfortunately they boil down to "you can't get there from here." There is no simple way to get the data type contained in a Field. The closest I was able to come was to do this for the quick-n-dirty:

Function Scripting()
    Dim rs As DAO.Recordset
    Dim ff As String
    Dim fieldType As String
    ff = "c:\Users\me\Desktop\Structure.txt"
    Open ff For Output As #1
    Set rs = CurrentDb.OpenRecordset("myTable")
    For x = 0 To rs.Fields.Count - 1
        Print #1, rs.Fields(x).Name & vbTab & TypeName(rs.Fields(x).Value) & vbTab & rs.Fields(x).Size
    Next
    Close #1
    rs.Close
    Set rs = Nothing
End Function

You would be well advised go to Allen Browne's excellent page and grab his FieldTypeName() function for a better solution.

Thanks to everyone (mehow, blackhawk, Chris Rolliston and Tim Williams) that chipped in on the answer.

like image 182
user1071914 Avatar answered Oct 27 '22 14:10

user1071914