Attribute VB_Name = "basCreateTable"
Option Compare Database
Option Explicit


Sub ADOX_CreateTable()

' Purpose : To create a table with 3 fields, and
'           setting a primary key using ADOX
    ' Notes :  !! You need to set a referece to Microsoft ADO Extentions
'           for DDL and Security (MSADOX.DLL)

'Declare objects
Dim cat As New ADOX.Catalog
Dim conn As New ADODB.Connection
Dim Idx As New ADOX.Index
Dim tbl As New ADOX.Table

'set the connection
Set conn = CurrentProject.Connection
Set cat.ActiveConnection = conn

'Create the table
With tbl
  .Name = "tblEmployees"
  .Columns.Append "numID", adInteger
  .Columns.Append "strFirstName", adWChar, 25
  .Columns.Append "strLastName", adWChar, 25
End With

'Add the table to the tables collection
cat.Tables.Append tbl
 
'Create the primary key on ID
With Idx
  .Name = "PrimaryKey"
  .Columns.Append "numID"
  .Columns("numID").SortOrder = adSortDescending
  .PrimaryKey = True
End With

'Append the Index\Primary Key.
cat.Tables("tblEmployees").Indexes.Append Idx

End Sub