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
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
NewTable.Name = "Employees"
With NewTable.Columns
.Append "EmployeeID", adInteger
.Append "EmployeeName", adVarWChar, 30
.Append "City", adVarWChar, 20
.Append "Address", adVarWChar, 40
.Append "Phone", adVarWChar, 15
With !EmployeeID
Set .ParentCatalog = cat
.Properties("Autoincrement") = True
End With
With !EmployeeName
Set .ParentCatalog = cat
.Properties("Nullable") = False
.Properties("Jet OLEDB:Allow Zero Length") = False
End With
End With
cat.Tables.Append NewTable
Set Indx = New ADOX.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
NewTable = cat.Tables("Customers")
Set Indx = New ADOX.Index
Indx.Name = "PrimaryKey"
Indx.PrimaryKey = True
Indx.Columns.Append "CustID"
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