Converting the DataTypeEnum Values to Equivalent VBA Types

Have you ever wanted to create your own custom ORM (object-relational mapper) framework in VBA?

If so, you'll need a way to convert database types to their equivalents in VBA.

Converting Database Types to String Descriptions

Before we convert the database types to VBA equivalents, let's first do the easy part:  converting the database type enum values to their string equivalents.  (This is one of those times where I really miss the reflection capabilities of VB.Net.)

Function DbTypeToString(DbType As DataTypeEnum) As String
    Select Case DbType
    Case dbAttachment: DbTypeToString = "Attachment"
    Case dbBigInt: DbTypeToString = "Big Int"
    Case dbBinary: DbTypeToString = "Binary"
    Case dbBoolean: DbTypeToString = "Boolean"
    Case dbByte: DbTypeToString = "Byte"
    Case dbChar: DbTypeToString = "Char"
    Case dbComplexByte: DbTypeToString = "Complex Byte"
    Case dbComplexDecimal: DbTypeToString = "Complex Decimal"
    Case dbComplexDouble: DbTypeToString = "Complex Double"
    Case dbComplexGUID: DbTypeToString = "Complex GUID"
    Case dbComplexInteger: DbTypeToString = "Complex Integer"
    Case dbComplexLong: DbTypeToString = "Complex Long"
    Case dbComplexSingle: DbTypeToString = "Complex Single"
    Case dbComplexText: DbTypeToString = "Complex Text"
    Case dbCurrency: DbTypeToString = "Currency"
    Case dbDate: DbTypeToString = "Date"
    Case dbDecimal: DbTypeToString = "Decimal"
    Case dbDouble: DbTypeToString = "Double"
    Case dbFloat: DbTypeToString = "Float"
    Case dbGUID: DbTypeToString = "GUID"
    Case dbInteger: DbTypeToString = "Integer"
    Case dbLong: DbTypeToString = "Long"
    Case dbLongBinary: DbTypeToString = "Long Binary"
    Case dbMemo: DbTypeToString = "Memo"
    Case dbNumeric: DbTypeToString = "Numeric"
    Case dbSingle: DbTypeToString = "Single"
    Case dbText: DbTypeToString = "Text"
    Case dbTime: DbTypeToString = "Time"
    Case dbTimeStamp: DbTypeToString = "TimeStamp"
    Case dbVarBinary: DbTypeToString = "VarBinary"
    Case Else: DbTypeToString = "Unknown"
    End Select
End Function

Converting Database Types to Equivalent VBA Data Types

This next function can be used as a building block in the development of a simple object relational mapper.  The output can be used to build class properties that correspond directly to fields in the database:

Function DbTypeToVbaType(DbType As DataTypeEnum) As String
    
    Select Case DbType
#If VBA7 Then
  #If Win64 Then
    Case dbBigInt: DbTypeToVbaType = "LongLong"
  #Else
    Case dbBigInt: DbTypeToVbaType = "Variant  'Decimal"
  #End If
#Else
    Case dbBigInt: DbTypeToVbaType = "Variant  'Decimal"
#End If
    Case dbBinary: DbTypeToVbaType = "Byte() 'Binary"  'byte array
    Case dbBoolean: DbTypeToVbaType = "Boolean"
    Case dbByte: DbTypeToVbaType = "Byte"
    Case dbChar: DbTypeToVbaType = "String"
    Case dbCurrency: DbTypeToVbaType = "Currency"
    Case dbDate: DbTypeToVbaType = "Date"
    Case dbDecimal: DbTypeToVbaType = "Variant 'Decimal"
    Case dbDouble: DbTypeToVbaType = "Double"
    Case dbFloat: DbTypeToVbaType = "Double"
    Case dbGUID: DbTypeToVbaType = "String 'GUID"
    Case dbInteger: DbTypeToVbaType = "Integer"
    Case dbLong: DbTypeToVbaType = "Long"
    Case dbLongBinary: DbTypeToVbaType = "Byte() 'Long Binary" 'byte array
    Case dbMemo: DbTypeToVbaType = "String"
    Case dbNumeric: DbTypeToVbaType = "Variant 'Numeric"
    Case dbSingle: DbTypeToVbaType = "Single"
    Case dbText: DbTypeToVbaType = "String"
    Case dbTime: DbTypeToVbaType = "Date 'Time"
    Case dbTimeStamp: DbTypeToVbaType = "Date 'TimeStamp"
    Case dbVarBinary: DbTypeToVbaType = "Byte() 'Var Binary"
    Case Else: DbTypeToVbaType = "'Not Supported: " & DbTypeToString(DbType)
    End Select
End Function

Allen Browne's Been Down This Trail

I was not aware of it until I started doing research for this article, but Allen Browne has a TableInfo() function that uses a function very similar to the DbTypeToString function above.  

Allen Browne's entire website is a treasure trove of great Access tips.  Be sure to check it out if you haven't already.

Further reading

VBA and BigInt Access Data Type

Stefan Hoffmann

External references

Microsoft Access tips: TableInfo() function

How to list a table schema in a Microsoft Access database, showing the field names, types, size, and descriptions. Includes code to give the field type names.

Microsoft Access help, tutorials, examples

Free help and tutorials for Microsoft Access. Tips for beginners and database developers. Downloadable examples and utilities. Flaws, bugs, traps, code, design, queries, forms, reports, version and upgrade information.

Image by Silvia from Pixabay