How To Add Tables To An MS Access Database Using ADOX and Visual Basic

Let us add table to database created in my post How To Create MS Access Database Using ADOX and Visual Basic

Dim ADOXcatalog As New ADOX.Catalog

Dim ADOXtable As New ADOX.Table

Dim ADOXindex As New ADOX.Index

On Error GoTo errhandler

ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "MyLibrary.mdb")

On Error Resume Next

ADOXcatalog.ActiveConnection = _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

& "MyLibrary.mdb"

'name table, append fields to table

ADOXtable.Name = "Books"

'ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)

ADOXtable.Columns.Append("Book", ADOX.DataTypeEnum.adVarWChar, 200)

ADOXtable.Columns.Append("Author", ADOX.DataTypeEnum.adVarWChar, 30)

'append tables to database

ADOXcatalog.Tables.Append(ADOXtable)

MsgBox(ADOXtable.Properties.Count.ToString)

'internal index on two fields

'ADOXindex.Name = "TwoColumnsIndex" 'name of index

'ADOXindex.Columns.Append("Author")

'ADOXindex.Columns.Append("Book")

ADOXtable.Indexes.Append(ADOXindex)

errhandler:

If Err.Number = -2147217897 Then

MsgBox("Database already exists")

ElseIf Err.Number <> 0 Then

MsgBox("Err " & Err.Description & "; operation not complete")

End If

ADOXtable = Nothing

ADOXindex = Nothing

ADOXcatalog = Nothing

Since database already exists, error handler will be invoked and this message will be prompted. Cleaning is very important by setting to Nothing