Promote Your Blog
Google

Saturday, January 28, 2012

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

No comments:

Post a Comment