How to obtain the name of a const? 3

[rejome]

Hi,

Using ADODB, everybody knows that adBinary has the value 128. By using the number, 128, I'd like to obtain the corresponding name of the const, adBinary. How can I do that?

 

[CCLINT]

Use a Select Case, listing one constant for each case.

 

[rejome]

There is more than 40 const. Is there any chance I can use the definition table of VB, WITHOUT using a select case?

 

[CCLINT]

Why do you need them? Or, how do you want to use them?
I'm trying to imagine a situation where this is needed.
Then maybe you can be helped further/better with your situation.

If rs.Fields(0).Type = adBinary Then

 

[rejome]

Using a recordset, I want to list all the type of the fields used in a table. By this way, the user will be able to decide what kind of field will corresponding to, for example adBinary.

I want to create a SQL to create a table, using some conversion to adBinary and, for example, number type.

 

[CCLINT]

Realize that those constant names do not exist any longer in the compiled version.
You could use the TypeName() function for the basic field types, but would have a problem if there are any NULLs, and you would still have to map them to the ADO constant equivalents.
You can create a UDT holding the value and a name. There shouldn't be any problem doing this, but a Select Case is better, and this shouldn't be a problem - it's only 40 names which you need to type once in one single proceedure.

 

[rejome]

Well CCLINT, you really stick with the select case I'll go with this cause TypeName doesn't do what I want. I just wanted to use the table definition cause the const will probably not be the same if the version of ADO is different of mine.

Anyway, I wanted to do that:
-Table in Microsoft: Key1, type Numeric
-The corresponding type with ADO is adBinary.
-I want to create the same table, on Oracle, but the type Numeric is not supported.
-So, I want to create an equivalent SQL. CREATE TABLE Test (Key1 NUMBER).
- The thing is, I've created a table where the type of Microsoft Access's fields are listed and the user can type the equivalent. But the number 128 doesn't mean anything. adBinary is better and seeing this, the user could now associate easily the type NUMBER.

But hey, thanks anyway

 

[strongm]

You could always check out Microsoft's TypeLib Information Library. We've given exampple in the past in this forum of using this library to list all the properties of an object, but you can also use it to list all the members of an object's enumerations. The library is included with VB, but the help file isn't; you can find it here:

http://download.microsoft.com/download/vb60pro/update/6.0/w9x2k/en-us/tlbinfht.exe

 

[CCLINT]

But I do not think it will necessarily help with the posted problem, or at least not make it easier...just maybe satisfy "persistance"...instead of drowning a horse...

 

[strongm]

Sure it will. You need a form and a command button, and to have set a reference to the Microsoft TypeLib Information library:
[tt]
Option Explicit

Private Sub Command1_Click()
' Look up the name of any ADO enumeration member via its value
' Pass the name of the enumeration you are interested in, and the value
MsgBox GetADOEnumMemberName("DataTypeEnum", 128)
End Sub


Public Function GetADOEnumMemberName(strEnumName As String, lVal As Long) As String

Dim TLIApp As TLIApplication
Dim myConstant As ConstantInfo
Dim EnumMember As MemberInfo
Dim ADOTLI As TypeLibInfo

Set TLIApp = New TLIApplication

' Get our type library info from the relevant file
' This is the line that makes this example specific to the ADO object library
Set ADOTLI = TLI.TypeLibInfoFromFile("c:\program files\common files\system\ado\msado15.dll&quot '(TLInfo.Guid, TLInfo.MajorVersion, TLInfo.MinorVersion, 1024)

' Loop through members of libraries constants
For Each myConstant In ADOTLI.Constants
If myConstant.TypeKind = TKIND_ENUM And myConstant.Name = strEnumName Then
For Each EnumMember In myConstant.Members
If EnumMember.Value = lVal Then
GetADOEnumMemberName = EnumMember.Name
End If
' List all members of this enum in debug window if you like
' Debug.Print EnumMember.Name, EnumMember.Value
Next
End If
Next
End Function

 

[CCLINT]

Ok, I had assumed this before (a while back, thanks also then to strongm who had posted it then), but it leaves you still with having to use a Select Case, or your own predefined list (you cannot just pick any from the list and expect to be able to use it).

It did get me thinking again, so I am working on something that will combine another technique along with strongm's example using the TLIApplication.

 

[rejome]

Well thanks for the help. For now on, I stick with the select case (CCLINT's idea) but I've tried the TypeLibInfo. Very useful. But like CCLINT said, you have to use a select case anyway. Trying too to combine all the things togheter.

By the way, I'm glad my post makes some of you guys thinking really hard It means that my question IS difficult to solve...

 

[CCLINT]

Ok. This should provide a list(array) of all Provider Supported DataTypes, with ADO's Constant name and the Provider's Type Name.
It uses OpenSchema and strongm's posted function: GetADOEnumMemberName
(You will also need to use something like FSO to find the special folder "c:\Program Files" and then search the complete tree, as the folders will have diffrent names depending on the language).
[blue]

Public Function ProviderSupportedDataTypes(conn As ADODB.Connection) As Variant
    Dim rsADO           As ADODB.Recordset
    Dim sTestCompareFld As String
    Dim iCnt            As Integer
    Dim varList         As Variant
    
    Set rsADO = conn.OpenSchema(adSchemaProviderTypes, Array(Empty, Empty))
    With rsADO
        Do Until .EOF
            .MoveNext
            If Not .EOF Then iCnt = iCnt + 1
        Loop
        .MoveFirst
        ReDim varList(iCnt, 4)
        iCnt = 0
        
        Do Until .EOF

[green]
' Dim fld As ADODB.Field
' For Each fld In rsADO.Fields
' Debug.Print fld.Name & ": " & fld.Value
' Next fld
'Debug.Print vbCr
'Listing only the basic properties needed.[blue]

            varList(iCnt, 0) = .Collect("DATA_TYPE") 'Type Value
            varList(iCnt, 1) = GetADOEnumMemberName("DataTypeEnum", .Collect("DATA_TYPE")) 'ADO Type
            varList(iCnt, 2) = .Collect("TYPE_NAME") 'Provider Type Name
            If .Collect("IS_LONG") Then varList(iCnt, 3) = "BLOB" 'Long Type
            varList(iCnt, 4) = .Collect("BEST_MATCH") 'True/False
            iCnt = iCnt + 1
            .MoveNext
        Loop
        ProviderSupportedDataTypes = varList
        .Close
    End With
    Set rsADO = Nothing
End Function

[black]
Notice that with JET/ACCESS Mdb there not even nearly the same amount of DataTypes supported as with SQL Server.

You could do it the other way around:
List all ADO DataTypes using GetADOEnumMemberName(), and then allow the user to pick what they want, then check if the provider supports the type, and if not, map it to a type which is supported.
This mapping is what happens when you link tables from a JET Mdb to SQL Server anyways, or if ADO doesn't support a DataType.
For instance, the adTinyInt and adSmallInt both map to JET's Interger Type.
However, you need something like a Select Case for this.

 

[deulyd]

or do it manually!

Public Function RetrieveName(ID As ADODB.DataTypeEnum) As String
Dim strRet As String

Select Case ID

Case adArray: strRet = "adArray"
Case adBigInt: strRet = "adBigInt"
Case adBinary: strRet = "adBinary"
Case adBoolean: strRet = "adBoolean"
Case adBSTR: strRet = "adBSTR"
Case adChapter: strRet = "adChapter"
Case adChar: strRet = "adChar"
Case adCurrency: strRet = "adCurrency"
Case adDate: strRet = "adDate"
Case adDBDate: strRet = "adDBDate"
Case adDBTime: strRet = "adDBTime"
Case adDBTimeStamp: strRet = "adDBTimeStamp"
Case adDecimal: strRet = "adDecimal"
Case adDouble: strRet = "adDouble"
Case adEmpty: strRet = "adEmpty"
Case adError: strRet = "adError"
Case adFileTime: strRet = "adFileTime"
Case adGUID: strRet = "adGUID"
Case adIDispatch: strRet = "adIDispatch"
Case adInteger: strRet = "adInteger"
Case adIUnknown: strRet = "adIUnknown"
Case adLongVarBinary: strRet = "adLongVarBinary"
Case adLongVarChar: strRet = "adLongVarChar"
Case adLongVarWChar: strRet = "adLongVarWChar"
Case adNumeric: strRet = "adNumeric"
Case adPropVariant: strRet = "adPropVariant"
Case adSingle: strRet = "adSingle"
Case adSmallInt: strRet = "adSmallInt"
Case adTinyInt: strRet = "adTinyInt"
Case adUnsignedBigInt: strRet = "adUnsignedBigInt"
Case adUnsignedInt: strRet = "adUnsignedInt"
Case adUnsignedSmallInt: strRet = "adUnsignedSmallInt"
Case adUnsignedTinyInt: strRet = "adUnsignedTinyInt"
Case adUserDefined: strRet = "adUserDefined"
Case adVarBinary: strRet = "adVarBinary"
Case adVarChar: strRet = "adVarChar"
Case adVariant: strRet = "adVariant"
Case adVarNumeric: strRet = "adVarNumeric"
Case adVarWChar: strRet = "adVarWChar"
Case adWChar: strRet = "adWChar"

End Select

RetrieveName = strRet

End Function

 

[strongm]

How is that an improvement?

 

[DrJavaJoe]

I thought CCLINT was back for a second, too bad. I wonder what ever happened to him and vb5prgrmr.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[tsdragon]

If you are using MZTools (which I STRONGLY recommend) you can use their select case helper. It works REALLY well with enums, which is probably what you really should be using here. If you do a "Select Case enum-name and then hit the select case helper it will create a case statement for every item in the enum. It can be a REAL time saver.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[deulyd]

Cool, I've never heard of MZTools but it is quite usefull! Thanks tsdragon!

 

[tsdragon]

I learned about MZTools from this forum. It has some terrific features.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.