|
What is Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security 2.1 (ADOX)
and how to use it to create/add Tables in a MS Access database
The article briefly discusses about ADOX and shows you how to
create, and modify the structure of Access databases.
Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security 2.1 are
extensions to the base ADO functionality that allow you to perform data definition language (DDL)
functions such as creating databases, and creating, modifying, or deleting tables, views (queries), stored procedures,
indexes, and relationships.
ADOX Also includes security objects to maintain security on user and group accounts,
and to grant and revoke permissions on objects.
You can view, create, and modify the structure of Access databases by using ADOX objects, methods, and properties.
The first thing you need to do before getting started is to add a reference to Microsoft ADO Ext. 2.x for DDL and Security
Obect Lirary from VB references dialog.
(goto Project-->References-->Microsoft SQLDMO Object Library)
Declare object variables to hold a Catalog and a Table
Dim catDB As ADOX.Catalog
Dim tblNew As ADOX.Table
Instantiate the Catalog object and set it's connection
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
Here we create a Table and append some fields to it's columns collection
When creating columns, you need to specify an
ADOX datatype corresponding to the Access datatype. Here is the list of datatypes in ADOX that math the
Access datatypes.
| Access user interface data type | ADOX data type |
| Text | adVarWChar |
| Memo | adLongVarWChar |
| Number(FieldSize = Byte) | adUnsignedTinyInt |
| Number (FieldSize = Decimal) | adDecimal |
| Number or AutoNumber(FieldSize=Replication ID) | adGUID |
| Number (FieldSize = Integer) | adSmallInt |
| Number or AutoNumber (FieldSize = LongInteger) | adInteger |
| Number (FieldSize = Single) | adSingle |
| Number (FieldSize = Double) | adDouble |
| Yes/No | adBoolean |
| Currency | adCurrency |
| Data/Time | adDate |
| OLE Object | adLongVarBinary |
| Hyperlink | adLongVarWChar, plus ADOX provider-specific Column property set to Jet OLEDB:Hyperlink |
'Create a new Table object.
Set tblNew = New ADOX.Table
With tblNew
.Name = "Contacts"
'Create fields and append them to the Columns collection of the new Table object.
With .Columns
.Append "FirstName", adVarWChar
.Append "LastName", adVarWChar
.Append "Phone", adVarWChar
.Append "Notes", adLongVarWChar
End With
End With
Add the new Table to the Tables collection of the database
catDB.Tables.Append tblNew
Set tblNew = Nothing
Set catDB = Nothing
'Programmatically Creating or adding Tables to Access database using Microsoft ADO Ext. 2.1 for DDL and Security(ADOX)
Sub CreateAccessTable(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tblNew As ADOX.Table
Set catDB = New ADOX.Catalog
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
Set tblNew = New ADOX.Table
With tblNew
.Name = "Contacts"
With .Columns
.Append "FirstName", adVarWChar
.Append "LastName", adVarWChar
.Append "Phone", adVarWChar
.Append "Notes", adLongVarWChar
End With
End With
catDB.Tables.Append tblNew
Set tblNew = Nothing
Set catDB = Nothing
End Sub
|
