Adding Tables to MS Access database - ADOX
Google
Web www.mahipalreddy.com

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.

 

 

References

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)

Decalrations

Declare object variables to hold a Catalog and a Table

   Dim catDB As ADOX.Catalog
   Dim tblNew As ADOX.Table

Open connection to the access database

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

Create Table and Columns

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

Append Table

Add the new Table to the Tables collection of the database

	catDB.Tables.Append tblNew

Release references

   Set tblNew = Nothing
   Set catDB = Nothing


FULL CODE

	'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

Top

www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.