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

Материал из VB Эксперт

Перейти к: навигация, поиск

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