How to Create an Index using VBA

[Peter Hibbs]

Hi All

I am trying to create a PrimaryKey index using VBA.

I am using the adhCreatePrimaryKey() function from the Access 2000
Developer's Handbook (p1498).

The key fields are initially held in a string variable, for example :-

vKeys = "Field1" or

vKeys = "Field1,Field2" or

vKeys = "Field1,Field2,Field3" etc. There could be up to 10 fields (although
this would be unlikely I think).

The format for the function call from the book is :-

Debug.Print adhCreatePrimaryKey("MyTable", "PrimaryKey", "Field1",
"Field2")

Is there any way I can pass the field names from my vKeys variable to the
function. If modifying the function code itself is necessary that would be OK
(Hopefully Litwin, Getz, and Gilbert would not mind).

This is the first part of the function :-
--------------------------------------------------------
Public Function adhCreatePrimaryKey(strTableName As String, strKeyName As
String, ParamArray varFields() As Variant) As Boolean

' From Access 2000 Developer's Handbook by Litwin, Getz, and Gilbert (Sybex)
Copyright 1999. All rights reserved.

' Create a new primary key and its index for a table. If the table already
has a primary key, remove it.
' In:
' strTableName: name of the table with which to work
' strKeyName: name of the index to create
' varFields: one or more fields passed as a list of strings to add to
the collection of fields in the index.
' Out:
' Return value: True on success, False otherwise.

Dim idx As DAO.Index
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim varPK As Variant
Dim varIdx As Variant
Dim idxs As DAO.Indexes
Dim db As DAO.Database

On Error GoTo CreatePrimaryKey_Err

Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
Set idxs = tdf.Indexes

' Find out if the table currently has a primary key.
' If so, delete it now.
varPK = FindPrimaryKey(tdf)
If Not IsNull(varPK) Then
idxs.Delete varPK
End If
' Create the new index object.
Set idx = tdf.CreateIndex(strKeyName)

' Set the new index up as the primary key.
' This will also set:
' IgnoreNulls property to False,
' Required property to True,
' Unique property to True.
idx.Primary = True

' Now create the fields that make up the index, and append
' each to the collection of fields.
For Each varIdx In varFields
AddField idx, varIdx
Next varIdx

 

[Michael H]

Peter,

Is your delimiter always a comma? How about something like the following
(untested):

Replace this:
Public Function adhCreatePrimaryKey(strTableName As String, strKeyName As
String, ParamArray varFields() As Variant) As Boolean
With this:
Public Function adhCreatePrimaryKey(strTableName As String, strKeyName As
String, strFields As String) As Boolean

Replace this:
For Each varIdx In varFields
AddField idx, varIdx
Next varIdx
With this:
Dim i As Integer
For i = 0 To UBound(Split(strFields, ","))
AddField idx, Split(strFields, ",")(i)
Next i

When calling adhCreatePrimaryKey, use the vKeys as the strFields parameter.

-Michael

 

[aaron.kempf]

this is a trick fucking question right??

a) don't use VBA
b) use TSQL
c) don't use MDB
d) use SQL Server Books Online and lookup 'create index' syntax

MDB is for babies.
Don't be a baby.

-Aaron
ADP Nationalist

 

[Peter Hibbs]

Michael,

Very neat piece of code, works perfectly.

Thanks very much

 

[aaron.kempf]

TSQL is a much more elegant solution.

MDB is for babies

ADP rocks

-Aaron