Indexed Field: Creating via VBA

[kleky]

Hey guys,

I need to change the design of my table and make a field Indexed, both with or without duplicates. I think maybe TableDef is used but cant figure out how Im using 97

If anyone has any code that would be great.

cheers

Ian

 

[dwilliams]

Not exactly VBA but maybe a solution

The easiest way to do this is to insert a bit of SQL code into your VBA sub as follows:

Private Sub Command1_Click()

DoCmd.RunSQL "ALTER TABLE YourTable ADD ColName Varchar(30)
Comment:Your index needs a name
DoCmd.RunSQL "CREATE UNIQUE INDEX Name ON YourTable (ColName)

EndSub


That should do it - let me know how you get on

Cheers

Dae

 

[kleky]

Excellent, thankyou very much!

I had to change a column already present so I just used the latter line.

For the benefit of anyone else needing this, if you remove "UNIQUE" it will create an index that allows duplicates...and make sure you use different index names if creating more than one in the same table

muchos thankos

kleky