Create Table with AutoNumber Indexed Field

• Download source - 21.45 KB

Introduction

I wrote an article earlier about ActiveX control (DataGrid) includes (ComboBox) and (DTPicker) control to any column in the DataGrid. When I created a database file to test the ActiveX, I added AutoNumber and Indexed field (StudentID) to the table (Students). I found the following question for my article:

"I would like to know how to do index column in datagrid just like your example for the StudentID column?"

Now I shall try to answer this question.

Background

I think that there are more ways than one to create a unique index field.

First Way

At design time of our database file, with Microsoft Access we can select the type of field and its properties.

Second Way

From the menu of VB6, open Add-Ins, then choose  Visual Data Manager, then use VisData to change the properties of fields but the VisData manager is compatible with Access 97.

Third Way

With code, I try to explain how to add AutoNumber field or Indexed field to an existing table and how to add a new table to your database file.

To add a new field CustID to the existing table Customers:

strSql = "ALTER TABLE Customers ADD COLUMN CustID Integer" : type of field is Integer.

To add AutoNumber field CustID to existing table Customers, make sure that the table does not have another field as AutoNumber:

strSql = "ALTER TABLE Customers ADD COLUMN CustID COUNTER"

To add AutoNumber field as Primary Key CustID to an existing table Customers make sure that the table does not have another field as AutoNumber:

strSql = "ALTER TABLE Customers ADD COLUMN CustID COUNTER PRIMARY KEY"

Perhaps someone will ask: What can I do if I want to make an existing field in my table as the Primary Key?

I try to answer: You can create a unique index field when adding the following reference:
"Microsoft ADO Ext.2.8 for DDL and Security" to your project. You can read about this idea in the following lines.
Also I shall write about create new table and append fields to the table.

Using the Code

Add AutoNumber field as Primary Key: Make sure that you add the reference: "Microsoft ActiveX Data Objects 2.x"

Dim cn As ADODB.ConnectionDim DataFile As StringDim strCon As StringDim strSql As String
DataFile = App.Path + "\CustomDB.mdb"   
Set cn = New ADODB.Connection   
strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & ";"   
cn.Open strCon   	'add the field (CustomerID) as AutoNumber to the table (Customers),
		'and set it as Primary Key,   
		'make sure that table has not another field as AutoNumber:   
strSql = "ALTER TABLE Customers ADD COLUMN CustomerID COUNTER PRIMARY KEY"   
cn.Execute strSql
cn.Close   Set cn = Nothing

Create New Table

Make sure that you add the reference: "Microsoft ActiveX Data Objects 2.x"
and the reference: "Microsoft ADO Ext.2.8 for DDL and Security":

Dim strCon As String
Dim DataFile As String
Dim cn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim NewTable As ADOX.Table
Dim Indx As ADOX.Index

   DataFile = App.Path + "\CustomDB.mdb"
   Set cn = New ADODB.Connection
   strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & ";"
   cn.Open strCon

   Set cat.ActiveConnection = cn
   Set NewTable = New ADOX.Table
   'make sure that your database file has not table with same name,
   'of new table:
   NewTable.Name = "Employees"

   'Append Columns.
   With NewTable.Columns
      .Append "EmployeeID", adInteger 'Long Integer
      .Append "EmployeeName", adVarWChar, 30 'Text
      .Append "City", adVarWChar, 20 'Text
      .Append "Address", adVarWChar, 40 'Text
      .Append "Phone", adVarWChar, 15 'Text

      With !EmployeeID
         Set .ParentCatalog = cat
         .Properties("Autoincrement") = True 'AutoNumber.
      End With

      With !EmployeeName
         Set .ParentCatalog = cat
         .Properties("Nullable") = False 'Required.
         .Properties("Jet OLEDB:Allow Zero Length") = False
      End With
   End With

   cat.Tables.Append NewTable 'Save the table

   Set Indx = New ADOX.Index 'Create a primary key index
   Indx.Name = "PrimaryKey"
   Indx.PrimaryKey = True
   Indx.Columns.Append "EmployeeID"
   NewTable.Indexes.Append Indx

   Set Indx = Nothing
   Set NewTable = Nothing
   Set cat = Nothing

Set Existing Field as Primary Key

Make sure that the field does not have Null value and does not have duplicate values, else you will get an error.
Make sure that you add the two previous references.

Dim strCon As StringDim DataFile As StringDim cn As ADODB.ConnectionDim cat _
	As New ADOX.CatalogDim NewTable As ADOX.TableDim Indx As ADOX.Index

   DataFile = App.Path + "\CustomDB.mdb"
   Set cn = New ADODB.Connection
   strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & ";"
   cn.Open strCon

   Set cat.ActiveConnection = cn
   Set'Create a Primary Key index (Cannot contain Null value)
   NewTable = cat.Tables("Customers")

   Set Indx = New ADOX.Index
   Indx.Name = "PrimaryKey"
   Indx.PrimaryKey = True
   'make sure that table has field with the name "CustID" and cannot contain Null value,
   'or change following field name:
   Indx.Columns.Append "CustID"
   'if the field has Null value you have error.
   NewTable.Indexes.Append Indx

   Set Indx = Nothing
   Set NewTable = Nothing
   Set cat = Nothing

Remarks

When extracting the prjTest.zip file, you can see how the previous code works.

Last Words

I hope this article is useful and helps you in your applications. Please tell me if you have any ideas or if you find any problems. Thanks to CodeProject and thanks to all.

-- Mostafa Kaisoun
M_Kaisoun@hotmail.com