VBA/Excel/Access/Word/Access/ADO Data Type

adInteger and adWChar

 
Sub CreateTable()
    Dim tdf As ADOX.Table
    Dim idx As ADOX.Index
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tdf = New ADOX.Table
    With tdf
        .Name = "Foods"
        Set .ParentCatalog = cat
        .Columns.Append "ID", adInteger
        .Columns("ID").Properties("AutoIncrement") = True
        .Columns.Append "Description", adWChar
        .Columns.Append "Calories", adInteger
    End With
    cat.Tables.Append tdf
    Set idx = New ADOX.Index
    With idx
        .Name = "PrimaryKey"
        .Columns.Append "ID"
        .PrimaryKey = True
        .Unique = True
    End With
    tdf.Indexes.Append idx
    Set idx = Nothing
    Set cat = Nothing
End Sub

ADO Equivalents to Access Data Types

 
Microsoft Access Data Type     ADO Equivalent
Binary                         adBinary
Boolean                        adBoolean
Byte                           adUnsignedTinyInt
Currency                       adCurrency
Date                           adDate
Numeric                        adNumeric
Double                         adDouble
Small Integer                  adSmallInt
Integer                        adInteger
Long Binary                    adLongBinary
Memo                           adLongVarWChar
Single                         adSingle
Text                           adWChar

Create a table with ADOX.Table and ADO data type

 
Sub makeTable()
   Dim currCat As New ADOX.Catalog
   Dim newTable As New ADOX.Table
   Dim newKey As New ADOX.Key
   
   currCat.ActiveConnection = CurrentProject.Connection
   
   With newTable
      .Name = "tblTestTable"
      .Columns.Append "custNumber", adInteger
      .Columns("custNumber").ParentCatalog = currCat
      .Columns("custNumber").Properties("AutoIncrement") = True
      
      newKey.Name = "PrimaryKey"
      newKey.Columns.Append "custNumber"
      .Keys.Append newKey, adKeyPrimary
      
      .Columns.Append "custFirstName", adWChar
      .Columns.Append "custLastName", adWChar
     End With
     
     currCat.Tables.Append newTable
     
     Set currCat = Nothing
End Sub