creating a table

[deno]

how do i create a new table from within VBA?
i have a query all built i just don't know how to get the results into a table.

thanks,
deno

 

[Howlsta]

The code below will create a table, just change it so it fits the fields you require, then you can add your tuples from the query.

HTH

Rich

Sub CreateTable()
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim idx As ADOX.Index

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

' Create new Table
Set tdf = New ADOX.Table
' Add field to Table Definition

With tdf
.Name = "tblFoods"
Set .ParentCatalog = cat
.Columns.Append "FoodID", adInteger
.Columns("FoodID").Properties("AutoIncrement") = True
.Columns.Append "Description", adWChar
.Columns.Append "Calories", adInteger
End With

cat.Tables.Append tdf

Set idx = New ADOX.Index
idx.Name = "PrimaryKey"
idx.Columns.Append "FoodID"
idx.PrimaryKey = True
idx.Unique = True
tdf.Indexes.Append idx

Set idx = Nothing
Set cat = Nothing

End Sub